Displays information on the current wait states for all active database sessions.

SET LINESIZE 250
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20

SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
d.spid AS process_id,
a.wait_class,
a.seconds_in_wait,
a.state,
a.blocking_session,
a.blocking_session_status,
a.module,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
AND a.status = 'ACTIVE'
ORDER BY 1,2;

SET PAGESIZE 14