← Sessions & Locks
Oracle · Sessions & Locks
How to find the root blocker in a lock chain in Oracle
Walk an Oracle lock chain to the ultimate blocker at the head using FINAL_BLOCKING_SESSION.
Tested on11g12c18c19c21c23ai26ai
V$SESSION.FINAL_BLOCKING_SESSION (11.2+) points straight to the head of the lock chain — the session everyone else is ultimately waiting for. Kill this one and the chain unwinds.
SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program,
s.blocking_session AS immediate_blocker,
s.final_blocking_session AS root_blocker,
s.event, s.seconds_in_wait, s.sql_id
FROM v$session s
WHERE s.final_blocking_session IS NOT NULL
ORDER BY s.final_blocking_session, s.seconds_in_wait DESC;locksblockingfinal_blocking_session