SET pagesize 55
SET linesize 170
col SQL format a80
col SERVER heading 'SERVER' format a10
col EVENT heading 'WAITING' format a30 fold_after
col OSUSER heading 'OSUSER' format a8
col USERNAME heading 'USERNAME' format a8
col PID heading 'OSPID' format 99999
col DISK_READS heading 'DISK I/O' format 99999999
col BUFFER_GETS heading 'BUFFER|GETS' format 99999999
SELECT SUBSTR(V$SESSION.USERNAME,1,8) USERNAME,
V$SESSION.OSUSER OSUSER,
V$SESSION.SERVER SERVER,
V$SQLAREA.DISK_READS DISK_READS,
V$SQLAREA.BUFFER_GETS BUFFER_GETS,
SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,
V$SESSION.PROCESS PID,
V$SESSION_WAIT.EVENT EVENT,
V$SQLAREA.SQL_TEXT SQL
FROM V$SESSION_WAIT, V$SQLAREA, V$SESSION
WHERE V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND
V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND
V$SESSION.SID = V$SESSION_WAIT.SID (+) AND
V$SESSION.STATUS = 'ACTIVE' AND
V$SESSION_WAIT.EVENT != 'client message'
ORDER BY V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME;
Here goes a sample output from the above query,
SQL> SET pagesize 55
SQL> SET linesize 170
SQL> col SQL format a80
SQL> col SERVER heading 'SERVER' format a10
SQL> col EVENT heading 'WAITING' format a30 fold_after
SQL> col OSUSER heading 'OSUSER' format a8
SQL> col USERNAME heading 'USERNAME' format a8
SQL> col PID heading 'OSPID' format 99999
SQL> col DISK_READS heading 'DISK I/O' format 99999999
SQL> col BUFFER_GETS heading 'BUFFER|GETS' format 99999999
SQL>
SQL> SELECT SUBSTR(V$SESSION.USERNAME,1,8) USERNAME,
2 V$SESSION.OSUSER OSUSER,
3 V$SESSION.SERVER SERVER,
4 V$SQLAREA.DISK_READS DISK_READS,
5 V$SQLAREA.BUFFER_GETS BUFFER_GETS,
6 SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,
7 V$SESSION.PROCESS PID,
8 V$SESSION_WAIT.EVENT EVENT,
9 V$SQLAREA.SQL_TEXT SQL
10 FROM V$SESSION_WAIT, V$SQLAREA, V$SESSION
11 WHERE V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS AND
12 V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE AND
13 V$SESSION.SID = V$SESSION_WAIT.SID (+) AND
14 V$SESSION.STATUS = 'ACTIVE' AND
15 V$SESSION_WAIT.EVENT != 'client message'
16 ORDER BY V$SESSION.LOCKWAIT ASC, V$SESSION.USERNAME;
BUFFER
USERNAME OSUSER SERVER DISK I/O GETS LOCKWAIT OSPID WAITING
-------- -------- ---------- --------- --------- -------- ------------ ------------------------------
SQL
--------------------------------------------------------------------------------
SYS USER-PC\ DEDICATED 0 51 2072:5608 SQL*Net message to client
Administ
rator
SELECT SUBSTR(V$SESSION.USERNAME,1,8) USERNAME, V$SESSION.OSUSER OSUSER,
V$SESSION.SERVER SERVER, V$SQLAREA.DISK_READS DISK_READS, V$SQLARE
A.BUFFER_GETS BUFFER_GETS, SUBSTR(V$SESSION.LOCKWAIT,1,10) LOCKWAIT,
V$SESSION.PROCESS PID, V$SESSION_WAIT.EVENT EVENT, V$SQLAREA.SQL_
TEXT SQL FROM V$SESSION_WAIT, V$SQLAREA, V$SESSION WHERE V$SESSION.SQL_ADDR
ESS = V$SQLAREA.ADDRESS AND V$SESSION.SQL_HASH_VALUE = V$SQLAREA.HASH_VALUE
AND V$SESSION.SID = V$SESSION_WAIT.SID (+) AND V$SESSION.STATUS =
'ACTIVE' AND V$SESSION_WAIT.EVENT != 'client message' ORDER BY V$SESSION.LO
CKWAIT ASC, V$SESSION.USERNAME
