Oracle 11g 10g Database SQL Script to Find Out Active Sessions Waits

_____________________________________________________________________________________________________________________

This script outputs the current wait states for all active database sessions.
COLUMN dbusername FORMAT A12
COLUMN osuser FORMAT A12
COLUMN sid FORMAT 999999
COLUMN OS_pid FORMAT 999999
COLUMN serial# FORMAT 99999999
COLUMN wait_class FORMAT A12
COLUMN state FORMAT A17
COLUMN logon_time FORMAT A19
SET LINESIZE 250
SET PAGESIZE 500
SELECT NVL(sess.username, 'OracleSess') dbusername,
       sess.osuser,
       sess.sid,
       sess.serial#,
       proc.spid OS_pid,
       sess.seconds_in_wait,
       sess.wait_class,
       sess.blocking_session,
       sess.blocking_session_status,
       sess.module,
       TO_CHAR(sess.logon_Time,'DD-MON-YYYY HH24:MI:SS') logon_time
FROM   v$session sess, v$process proc
WHERE  sess.paddr  = proc.addr
AND    sess.status = 'ACTIVE'
ORDER BY 1, 2;

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google