Oracle: find blocking sessions and locks

A diagnostic query that shows which Oracle session is blocking which — with database and OS user — and how to release the lock.

When users report that a screen or a batch “hangs”, the first thing to check on the Oracle side is whether a session is waiting on a lock held by another one.

Find blocking sessions

The query joins V$LOCK with itself: a session holding a lock that blocks others (block = 1) is matched with the sessions requesting the same resource (request > 0, same id1 / id2). For each pair it returns the database user and OS user, and the sid, serial# of both sessions.

SELECT
  (select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
  a.sid || ', ' || (select serial# from v$session where sid=a.sid) sid_serial,
 ' is blocking ',
  (select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
  b.sid || ', ' || (select serial# from v$session where sid=b.sid) sid_serial
FROM
  v$lock a, v$lock b
WHERE
  a.block = 1
  AND b.request > 0
  AND a.id1 = b.id1
  AND a.id2 = b.id2;

Release the lock

Once the blocker is identified, the cleanest fix is to ask its user to commit or roll back. If that is not possible, a DBA can end the session using the sid, serial# returned above:

ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE;

The uncommitted work of the killed session is rolled back, and the waiting sessions continue.