How to find blocked and blocking sessions

by Granville Bonyata on September 22, 2018

1) Find the session id (SID), serial# and instance (inst_id) of your session that is hanging.

2) Is your session being blocked?

SELECT s.blocking_status,
       s.blocking_session,
       s.event
  FROM gv$session s
 WHERE s.sid = [789]
   AND s.serial# = [98543]
   AND s.inst_id = [1]

If any one of these three is true:

1) The blocking_status is VALID or GLOBAL,

2) There is a value in “blocking_session”

3) The event is an enqueue event (starts with “enq”) then another session is blocking this session from acquiring a lock.

4) If the blocking status is VALID, the blocking session should be easy to identify, as gv$session will have the blocking session info:

SELECT s.blocking_instance,
       s.blocking_session,
  FROM gv$session s
 WHERE s.sid = [6789]
   AND s.serial# = [9876543]
   AND s.inst_id = [1]

4) If there is no blocking_session identified in gv$session, we need to look at the lock table to determine who is blocking the session:

SELECT b.inst_id,
       CASE WHEN A.request != 0 THEN '-> '||a.sid|| ' (BLOCKED)' ELSE TO_CHAR(a.sid) END sid,
       a.id1,
       a.id2,
       CASE a.lmode
       WHEN 0 THEN a.lmode||' - None'
       WHEN 1 THEN a.lmode||' - null'
       WHEN 2 THEN a.lmode||' - row-S'
       WHEN 3 THEN a.lmode||' - row-X'
       WHEN 4 THEN a.lmode||' - share'
       WHEN 5 THEN a.lmode||' - S/Row-X'
       WHEN 6 THEN a.lmode||' - exclusive'
       ELSE a.lmode||' - Unknown'
       END Lock_mode,
       CASE a.block
       WHEN 0 THEN a.block||' - Not blocking'
       ELSE a.block||' - Blocking'
       END block_type,
       CASE a.request
       WHEN 0 THEN a.request||' - None'
       WHEN 1 THEN a.request||' - null'
       WHEN 2 THEN a.request||' - row-S'
       WHEN 3 THEN a.request||' - row-X'
       WHEN 4 THEN a.request||' - share'
       WHEN 5 THEN a.request||' - S/Row-X'
       WHEN 6 THEN a.request||' - exclusive'
       ELSE a.request||' - Unknown'
       END request,
       b.blocking_session,
       b.blocking_instance,
       b.blocking_session_status,
       b.program,
       b.username,
       b.status,
       b.event,
       b.module,
       b.action,
       o.owner,
       o.object_name,
       b.osuser,
       b.logon_time
 FROM gv$lock a,
      gv$session b,
       dba_objects o
 WHERE (a.id1, a.id2) IN (SELECT id1, id2
                            FROM gv$lock
                           WHERE lmode = 0)
   AND a.inst_id = b.inst_id
   AND a.sid = b.sid
   AND o.object_id(+) = b.row_wait_obj#
 ORDER BY a.id1, a.id2, a.request;

Quick explanation: If a session holds a lock that blocks another session then “block” is set to 1. The sessions being blocked will share the same values for id1/id2 as the blocker, and the blocked sessions’ request type will be non-zero.

Previous post: