DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, September 30, 2015

Oracle Waiting Session Information

--
-- Show all database session waits.
--
 
SET PAUSE on
SET PAUSE 'Press Return to Continue'
SET LINESIZE 300
SET PAGESIZE 60
COLUMN username FORMAT A20
COLUMN event FORMAT A25
COLUMN wait_class FORMAT A20
 
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_class,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM   v$session_wait sw,
v$session s
WHERE  s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC
/

Example Result:

USERNAME                    SID    SERIAL# EVENT                     WAIT_CLASS            WAIT_TIME SECONDS_IN_WAIT STATE
-------------------- ---------- ---------- ------------------------- -------------------- ---------- --------------- -------------------
(oracle)                    153          1 VKTM Logical Idle Wait    Idle                          0          231343 WAITING
EUSER                      498         35 SQL*Net message from clie Idle                          0          226907 WAITING
                                           nt

EUER                      269         81 SQL*Net message from clie Idle                          0          226907 WAITING
                                           nt

1 comment:

Amirtha rao said...

Thanks This technical post helps me to improve my skills set, thanks for this wonder article I expect your upcoming blog, so keep sharing.
Regards,
Oracle DBA Training in Chennai|Oracle Training|Oracle Training Institute in Chennai