← Sessions & Locks
Oracle · Sessions & Locks

How to get a human-readable summary of blocking sessions in Oracle

Print a one-line "X is blocking Y" summary for every Oracle blocking session across the RAC, joined from GV$LOCK and GV$SESSION.

Tested on11g12c18c19c21c23ai26ai

Compact ''who is blocking whom'' output — one line per blocker-waiter pair, formatted as ''user@machine (SID=n) is blocking user@machine (SID=n)''. Fast to scan when you have many blockers at once and just want a picture of the situation.

SELECT s1.username || '@' || s1.machine || ' (SID=' || s1.sid || ')'
       || ' is blocking '
       || s2.username || '@' || s2.machine || ' (SID=' || s2.sid || ')' AS blocking_status
FROM   gv$lock    l1,
       gv$session s1,
       gv$lock    l2,
       gv$session s2
WHERE  s1.sid    = l1.sid
AND    s2.sid    = l2.sid
AND    l1.block   = 1
AND    l2.request > 0
AND    l1.id1     = l2.id1
AND    l1.id2     = l2.id2;
locksblockinggv$lockgv$session