set line 200

col spid for 999999
col TERMINAL for a15
col pid for 999999
col sid_serial for a15
col USERNAME for a15
col osuser for a10
col STATUS for a10
col time for a15
col current_statement for a60
col program_ex  for a25
col program  for a25
col lockwait  for a15

SELECT p.spid , 
DECODE(LOWER(SUBSTR(s.terminal,1,7)), 'unknown', s.machine, s.terminal) AS terminal, 
P.PID, 
TO_CHAR(S.SID) || ',' || TO_CHAR(S.SERIAL#) sid_serial, 
S.USERNAME, 
osuser, 
S.STATUS, 
DECODE(s.status, 'INACTIVE', 'Since ' || TO_CHAR(sysdate - (s.last_call_et) / 86400,'mm/dd/yy hh24:mi:ss') time , 
DECODE(S.COMMAND, 
0, 'NONE', 1, 'CREATE TABLE', 2, 'INSERT', 
3, 'SELECT', 4, 'CREATE CLUSTER', 5, 'ALTER CLUSTER', 
6, 'UPDATE', 7, 'DELETE', 8, 'DROP CLUSTER', 
9, 'CREATE INDEX', 10, 'DROP INDEX', 11, 'ALTER INDEX', 
12, 'DROP TABLE', 13, 'CREATE SEQUENCE', 14, 'ALTER SEQUENCE', 
15, 'ALTER TABLE', 16, 'DROP SEQUENCE', 17, 'GRANT', 
18, 'REVOKE', 19, 'CREATE SYNONYM', 20, 'DROP SYNONYM', 
21, 'CREATE VIEW', 22, 'DROP VIEW', 23, 'VALIDATE INDEX', 
24, 'CREATE PROCEDURE', 25, 'ALTER PROCEDURE', 26, 'LOCK TABLE', 
27, 'NO OPERATION', 28, 'RENAME', 29, 'COMMENT', 
30, 'AUDIT', 31, 'NOAUDIT', 
32, 'CREATE EXTERNAL DATABASE', 33, 'DROP EXTERNAL DATABASE', 
34, 'CREATE DATABASE', 35, 'ALTER DATABASE', 
36, 'CREATE ROLLBACK SEGMENT', 37, 'ALTER ROLLBACK SEGMENT', 
38, 'DROP ROLLBACK SEGMENT', 39, 'CREATE TABLESPACE', 
40, 'ALTER TABLESPACE', 41, 'DROP TABLESPACE', 42, 'ALTER SESSION', 
43, 'ALTER USER', 44, 'COMMIT', 45, 'ROLLBACK', 
46, 'SAVEPOINT', 47, 'PL/SQL EXECUTE', 48, 'SET TRANSACTION', 
49, 'Alt Sys SWITHC LOG', 50, 'EXPLAIN', 51, 'CREATE USER', 
52, 'CREATE ROLE', 53, 'DROP USER', 54, 'DROP ROLE', 
55, 'SET ROLE', 55, 'CREATE SCHEMA', 57, 'CREATE CONTROL FILE', 
58, 'ALTER TRACING', 59, 'CREATE TRIGGER', 60, 'ALTER TRIGGER', 
61, 'DROP TRIGGER', 62, 'ANALYZE TABLE', 63, 'ANALYZE INDEX', 
64, 'ANALYZE CLUSTER', 65, 'CREATE PROFILE', 66, 'DROP PROFILE', 
67, 'ALTER PROFLE', 68, 'DROP PROCEDURE', 69, 'DROP PROCEDURE', 
70, 'ALTER RESOURCE COST', 71, 'CREATE SNAPSHOT LOG', 72, 'ALTER SNAPSHOT LOG', 
73, 'DROP SNAPSHOT LOG', 74, 'CREATE SNAPSHOT', 75, 'ALTER SNAPSHOT', 
76, 'DROP SNAPSHOT', 77, 'CREATE TYPE', 78, 'DROP TYPE', 
79, 'ALTER ROLE', 80, 'ALTER TYPE', 81, 'CREATE TYPE BPDY', 
82, 'ALTER TYPE BODY', 83, 'DROP TYPE BODY', 84, 'DROP LIBRARY', 
85, 'TRUNCATE TABLE', 
86, 'TRUNCATE CLUSTER', 88, 'ALTER VIEW', 91, 'CREATE FUNCTION', 
92, 'ALTER FUNCTION', 93, 'DROP FUNCTION', 94, 'CREATE PACKAGE', 
95, 'ALTER PACKAGE', 96, 'DROP PACKAGE', 97, 'CREATE PACKAGE BODY', 
98, 'ALTER PACKAGE BODY', 99, 'DROP PACKAGE BODY', 100, 'LOGON', 
101, 'LOGOFF', 102, 'LOGOFF BY CLEANUP', 103, 'SESSION REC', 
104, 'SYSTEM AUDIT', 105, 'SYSTEM NOAUDIT', 106, 'AUDIT DEFAULT', 
107, 'NOAUDIT DEFAULT', 108, 'SYSTEM GRANT', 109, 'SYSTEM REVOKE', 
110, 'CREATE PUBLIC SYNONYM', 111, 'DROP PUBLIC SYNONYM', 
112, 'CREATE PUBLIC DATABASE LINK', 113, 'DROP PUBLIC DATABASE LINK', 
114, 'GRANT ROLE', 115, 'REVOKE ROLE', 116, 'EXECUTE PROCEDURE', 
-58, 'PURGE DBA RECYCLEBIN', -67, 'MERGE', 
TO_CHAR(s.command) || ' - Undefined') ) current_statement 
,s.program program_ex 
,SUBSTR(p.program,(INSTR(p.program,'(')+1),(INSTR(p.program,')')-INSTR(p.program,'('))-1) program 
,lockwait 
FROM V$SESSION S, 
V$PROCESS P 
WHERE s.paddr = p.addr 
AND s.status <> 'INACTIVE' 
ORDER 
BY s.username, s.status, 
s.last_call_et 



COLUMN sid_ser FORMAT a10 HEADING ' SID/Ser' 
COLUMN sid FORMAT 99999 HEADING ' SID' 
COLUMN username FORMAT a6 HEADING 'Oracle|User' 
COLUMN osuser FORMAT a10 TRUNC HEADING 'O/S User' 
COLUMN machine FORMAT a10 HEADING 'Machine' 
COLUMN program FORMAT a20 HEADING 'Program' 
COLUMN F_Ground FORMAT 99999 HEADING 'F''Ground|Process' 
COLUMN B_Ground FORMAT 99999 HEADING 'B''Ground|Process' 
COLUMN sql_text FORMAT a45 word_wrap HEADING 'SQL Text' 
COLUMN disk_reads FORMAT 99,999 HEADING 'Disk|Reads|(*1000)' 
COLUMN buffer_gets FORMAT 9,999,999 HEADING 'Buffer|Gets|(*1000)' 
COLUMN rows_processed FORMAT 99,999 HEADING 'Rows|Processed|(*1000)' 
COLUMN sorts FORMAT 99,999,999 HEADING 'Sorts' 

TTITLE Center 'SQL Currently Executing' 

SELECT /*+ ORDERED */
s.sid || ',' || s.serial# as sid_ser,
s.username,
s.osuser,
x.sql_text,
x.disk_reads / 1000 AS disk_reads,
x.buffer_gets / 1000 AS buffer_gets,
x.rows_processed / 1000 AS rows_processed,
x.sorts
,x.address
FROM v$session S,
v$process P,
v$sql X
WHERE s.type != 'BACKGROUND'
--AND s.username LIKE UPPER(NVL('&oracle_user%', '%'))
--AND LOWER(s.osuser) LIKE LOWER(NVL('&os_user%', '%'))
--AND s.sid LIKE NVL('&sid', '%')
AND s.sql_address = x.address
AND s.sql_hash_value = x.hash_value
--AND s.program like 'T%'
-- AND s.username NOT IN ('SYS','SYSTEM')
AND s.paddr = p.addr
ORDER
BY S.sid
/




