Home › Find an Oracle blocking session with dba_blockers and v$lock

Find an Oracle blocking session with dba_blockers and v$lock 

Dr. Xi
Joined:
04/09/2007
Posts:
703

March 31, 2010 15:35:53    Last update: March 31, 2010 15:35:53
DBA_BLOCKERS displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.

ColumnDatatypeDescription
HOLDING_SESSIONNUMBERSession holding a lock


SQL> select * from dba_blockers;

HOLDING_SESSION
---------------
            364

SQL> select * from v$lock where sid = 364;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
978B3CBC 978B3CE8        364 AE         99          0          4          0       2531          0
00502BC4 00502BF4        364 TM      73541          0          3          0       2531          0
96CF4888 96CF48C8        364 TX     196617     151100          6          0       2531          1

SQL> -- find the session that's blocked

SQL> select v1.*
  2  from v$lock v1, v$lock v2
  3  where v1.id1 = v2.id1 and v1.id2 = v2.id2
  4  and v2.sid = 364 and v2.block = 1;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
978B3E8C 978B3EB8        235 TX     196617     151100          0          6       2612          0
96CF4888 96CF48C8        364 TX     196617     151100          6          0       2640          1

SQL>
Share |
| Comment  | Tags
 
Easy email testing with http://www.ximailstop.com

Related Notes