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.