Finding object locks and blocking sessions in Oracle
Diagnostic queries to find the session holding a lock, the locked objects and the blocked sessions in Oracle.
When a session is waiting for a lock, you need to find the session that holds it, the objects involved and the sessions that are blocked. This article gathers the diagnostic queries used on Oracle.
Blocked and blocking sessions
The DBA_WAITERS view lists the waiting sessions and the session holding the lock:
SELECT waiting_session, holding_session, lock_type, mode_held
FROM dba_waiters;
Sessions whose BLOCKING_SESSION attribute is set are waiting on another session:
SELECT sid, serial#, username, status, blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL;
Locked objects
SELECT d.OBJECT_ID, d.OBJECT_NAME, l.SESSION_ID, l.ORACLE_USERNAME, l.LOCKED_MODE
FROM v$locked_object l, dba_objects d
WHERE d.OBJECT_ID = l.OBJECT_ID;
To link a session to its operating system process:
SELECT s.sid, s.serial#, s.username, p.spid AS os_process_id
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = <SID>;
Transactions and raw lock data
SELECT ADDR, KADDR, SID, TYPE, ID1, ID2, LMODE, BLOCK
FROM v$lock;
SELECT a.sid, a.saddr, b.ses_addr, a.username, b.xidusn, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
Kill the blocking session
Once the blocking session is identified, see the ORA-00054 article for the procedure to end it (ALTER SYSTEM KILL SESSION).
Notes
- A complete lock tree view (the
lock_tree.sqlscript) is available from oracle-base.com: https://oracle-base.com/dba/10g/lock_tree.sql ALTER TABLE ... DISABLE TABLE LOCKprevents DML on the table. Use it only with a clear reason.