select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ; SELECT sid, lmode acquired, request, blocking_session, SQL_TEXT FROM v$lock l JOIN v$session s USING (sid) LEFT JOIN v$sqlarea USING (sql_id) WHERE block = 1 OR request > 0 ;
Tree like:
WITH sessions_info AS (SELECT sid, blocking_session, row_wait_obj#, sql_id FROM v$session) SELECT DECODE(LEVEL, 1, 'Root Blocker') STATUS, LPAD (' ', LEVEL) || sid sid, object_name, SUBSTR (sql_text, 1, 50) sql_text FROM sessions_info s LEFT OUTER JOIN dba_objects ON (object_id = row_wait_obj#) LEFT OUTER JOIN v$sql USING (sql_id) WHERE sid IN (SELECT blocking_session FROM sessions_info) OR blocking_session IS NOT NULL CONNECT BY PRIOR sid = blocking_session START WITH blocking_session IS NULL;
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request /
SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
select * from dba_waiters;
select * from dba_blockers;
If the DBA_WAITERS and DBA_BLOCKERS doesn’t exist, you can execute the script below and create them:
@?/rdbms/admin/catblock.sql