denrepo

OracleBlocking & locks

Blocking tree

The whole chain as an indented tree, starting from the root blocker. When ten sessions are stuck, the one at the left margin is the one to deal with.

Not yet verified. How scripts are tested

ora-block-tree.sql 
1CLEAR COLUMNS2SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN tree      HEADING "SID,SERIAL# TREE" FORMAT A244COLUMN username  FORMAT A155COLUMN status    FORMAT A86COLUMN event     FORMAT A35 TRUNCATE7COLUMN sql_id    FORMAT A138COLUMN wait_secs HEADING "WAIT SECS" FORMAT 999,9999 10SELECT LPAD(' ', 2 * (LEVEL - 1)) || s.sid || ',' || s.serial# AS tree,11       s.username, s.status, s.event,12       NVL(s.sql_id, s.prev_sql_id) AS sql_id,13       s.seconds_in_wait AS wait_secs14FROM v$session s15START WITH s.blocking_session IS NULL16       AND s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)17CONNECT BY PRIOR s.sid = s.blocking_session;

Save it as ora-block-tree.sql and run it with SQL> @ora-block-tree.

Open in denrepo

Part of these runbooks

More Oracle scripts: Blocking & locks