COLUMN SID               format 999999
COLUMN serial            format 999999
COLUMN P1                format 999999999999
COLUMN P2                format 999999999999
COLUMN P3                format 999999999999
COLUMN EVENT             heading 'Wait|Event'    format a30
COLUMN USERNAME          heading 'User|Name'     format a10 TRUNCATE
COLUMN WAIT_CLASS        heading 'Wait|Class'    format a5  TRUNCATE
COLUMN blocker_chain_id  heading 'Blkng|Chn|ID'  format 99
COLUMN NUM_WAITERS       heading 'Blkd|Sess'     format a10
COLUMN INST_ID           heading 'I_#'           format 99
COLUMN IN_WAIT_SECS      heading 'Secs|in|Wait'  format 999,999
COLUMN CHAIN_ID          heading 'Chn|ID'        format 99
SELECT /*+ oracleblogwriter.com OEM_TOOLKIT blocking_sessions */ lpad( '  ' , LEVEL, '    ' ) ||s.USERNAME AS username,RPAD( '  ' , LEVEL , '        ' ) ||wc.num_waiters AS num_waiters, wc.sid sid, wc.sess_serial# AS SERIAL, s.INST_ID, s.sql_id, s.prev_sql_id, decode(s.wait_class,'Application','APPL', 'Other','OTHR', 'Idle','IDLE', 'Concurrency','CONCR', 'Cluster','CLSTR', 'Administrative','ADMN', 'User I/O','U_IO', 'System I/O','S_IO', 'Configuration','CONFIG') AS wait_class, s.event, wc.P1, wc.P2, wc.P3, wc.IN_WAIT_SECS, blocker_chain_id, chain_id FROM
v$wait_chains wc, gv$session s, gv$session bs, gv$instance i, gv$process p
WHERE wc.instance = i.instance_number (+) AND (wc.instance = s.inst_id (+) AND wc.sid = s.sid (+)  AND wc.sess_serial# = s.serial# (+))  AND (s.final_blocking_instance = bs.inst_id (+) AND s.final_blocking_session = bs.sid (+))  AND (bs.inst_id = p.inst_id (+) AND bs.paddr = p.addr (+)) AND ( num_waiters > 0 OR ( blocker_osid IS NOT NULL AND in_wait_secs > 1 ))
CONNECT BY PRIOR wc.sid=wc.blocker_sid
AND PRIOR wc.sess_serial#=wc.blocker_sess_serial#
AND PRIOR wc.instance = wc.blocker_instance START WITH wc.blocker_is_valid='FALSE'
ORDER BY wc.chain_id, LEVEL
/

- Até a próxima.

— Autor: Leonardo Lopes 24/04/2025 15H:56