Blocking Locks

Blocking Locks

A blocking lock occurs when a lock placed onfrom accessing the same object or objects. The information—it tells you which sessions are currentlyobject is presently waiting. You can combine in the V$SESSION tables, to find out who is holding

SQL> SELECT a.username, a.program, a.sid,
FROM v$session a, dba_blockers b
WHERE a.sid = b.holding_session;

DBA_BLOCKERS and DBA_WAITERS

SQL> SELECT waiting_session, blocking_session, lock_type
FROM DBA_BLOCKERS;

For 10g there is additional columns to check blocking session

select lpad(' ',3*(level-1)) SID SID, USERNAME, TERMINAL, CLIENT_INFO, EVENT
from V$SESSION
START WITH BLOCKING_SESSION_STATUS='VALID'
connect by prior BLOCKING_SESSION = SID