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.