← 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