Oracle Useful Views for identifying Blocking Sessions－DanBrother的部落格

Oracle Useful Views for identifying Blocking Sessions

DBA_BLOCKERS display a session if it is not waiting for a locked object but is holding on an object for which another session is waiting.
In an Oracle RAC environment, this only applies if the blocker is on the same instance.

DBA_WAITERS shows all the sessions that are waiting for a lock.

PS. Views were created by running $ORACLE_HOME/rdbms/admin/catblock.sql

Test Case:
[Session A]
select sid from v$mystat where rownum = 1;
       SID
----------
       303

create table test_lock
(id number(3),
status varchar2(1));

insert into test_lock(id,status) values(1,'n');
insert into test_lock(id,status) values(2,'n');
commit;
col id format 999
col status format a6
select * from test_lock;
ID STATUS
---- ------
1 n
2 n

update test_lock set status = 'y' where id = 2;

[Session B]
select sid from v$mystat where rownum = 1;
SID
----------
399

update test_lock set status = 'y' where id = 2;

[Session C]
col LOCK_TYPE format a12
col MODE_HELD format a10
col MODE_REQUESTED format a10
col LOCK_ID1 format 9999999999
col LOCK_ID2 format 9999999999
conn / as sysdba
select * from DBA_WAITERS;
>>
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2
--------------- --------------- ------------ ---------- ---------- ----------- -----------
399 303 Transaction Exclusive Exclusive 131084 13286

select * from DBA_BLOCKERS;
>>
HOLDING_SESSION
---------------
303

col SESSION_ID format 999999
col LOCK_TYPE format a12
col MODE_HELD format a10
col MODE_REQUESTED format a10
col LOCK_ID1 format a10
col LOCK_ID2 format a10
col BLOCKING_OTHERS format a20
select * from DBA_LOCK where session_id in (399,303) and blocking_others = 'Blocking';

SESSION_ID LOCK_TYPE    MODE_HELD MODE_REQUE LOCK_ID1   LOCK_ID2   LAST_CONVERT BLOCKING_OTHERS
---------- ------------ ---------- ---------- ---------- ---------- ------------ -------------------
       303 Transaction Exclusive None       131084     13286                37 Blocking

col sql_text format a64
select sid,serial#,b.sql_text
from v$session a, v$sqltext b
where sid = 399 and a.sql_address = b.address;

SID SERIAL# SQL_TEXT
---------- ---------- ----------------------------------------------------------------
399 8819 update test_lock set status = :"SYS_B_0" where id = :"SYS_B_1"

col OS_USER_NAME format a12
col ORACLE_USERNAME format a16
col ORACLE_USERNAME format a10
col PROCESS format a10
select * from v$locked_object;
    XIDUSN    XIDSLOT     XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS    LOCKED_MODE
---------- ---------- ---------- ---------- ---------- --------------- ------------ ---------- ------------
         2         12      13286      95155        303 TESTUSER        danbrother   1668:4940            3
         0          0          0      95155        399 TESTUSER        danbrother   612:5312             3

[Switch to Session A]
Session A> commit;

[Switch to Session B]
Session B> commit;

[Switch to Session C]
col LOCK_TYPE format a12
col MODE_HELD format a10
col MODE_REQUESTED format a10
Session C> select * from DBA_WAITERS;

no rows selected

Session C> select * from DBA_BLOCKERS;

no rows selected

[REFERENCE]
1. http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_3096.htm#REFRN23027
2. http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5078.htm#REFRN23306