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;
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google