LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon