DBA_WAITERS

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

Column	Datatype	NULL	Description
WAITING_SESSION	NUMBER		The waiting session
HOLDING_SESSION	NUMBER		The holding session
LOCK_TYPE	VARCHAR2(26)		The lock type
MODE_HELD	VARCHAR2(40)		The mode held
MODE_REQUESTED	VARCHAR2(40)		The mode requested
LOCK_ID1	VARCHAR2(40)		Lock ID 1
LOCK_ID2	VARCHAR2(40)		Lock ID 2

Note:

1. $ORACLE_HOME/rdbms/admin/catblock.sql The script creates these views, dba_locks and dba_blockers, along with the following views:

dba_kgllock

dba_lock (dba_locks is synonym for this)

dba_lock_internal

dba_dml_locks

dba_ddl_locks

dba_waiters

2. A number of Oracle views permits to detect locking problems.

V$SESSION_WAIT When a session is waiting on a resource, it can be found waiting on the enqueue wait event, e.g. SELECT * FROM V$SESSION_WAIT WHERE EVENT = 'enqueue';

- SID identifier of session holding the lock

- P1, P2, P3 determine the resource when event = 'enqueue'

- SECONDS_IN_WAIT gives how long the wait did occurs

V$SESSION session information and row locking information

- SID, SERIAL# identifier of the session

- LOCKWAIT address of the lock waiting, otherwise null

- ROW_WAIT_OBJ# object identified of the object we are waiting on (object_id of dba_objects)

- ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# file_id , block_id and row location within block of the locked row

V$LOCK list of all the locks in the system

- SID identifier of session holding the lock

- TYPE, ID1 and ID2 determine the resource

- LMODE and REQUEST indicate which queue the session is waiting on, as follows:

LMODE > 0, REQUEST = 0 owner

LMODE = 0, REQUEST > 0 acquirer

LMODE > 0, REQUEST > 0 converter

- CTIME time since current mode was converted (see Note 223146.1)

- BLOCK are we blocking another lock

BLOCK = 0 non blocking

BLOCK = 1 blocking others

DBA_LOCK or DBA_LOCKS formatted view on V$LOCK (created via $ORACLE_HOME/rdbms/admin/catblock.sql)

- SESSION_ID == SID in V$LOCK

- LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK

- MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK

- LAST_CONVERT == CTIME of V$LOCK

- BLOCKING_OTHERS formatted value of BLOCK from V$LOCK

V$TRANSACTION_ENQUEUE subset of V$LOCK for the blocking TX resources only (same description as for the V$LOCK view)
V$ENQUEUE_LOCK subset of V$LOCK for the system resources only and blocked TX resources only. (same description as for the V$LOCK view)
DBA_DML_LOCKS subset of the V$LOCK for the DML (TM) locks only (created via $ORACLE_HOME/rdbms/admin/catblock.sql
- same description as the DBA_LOCK view)
V$LOCKED_OBJECT same info as DBA_DML_LOCKS, but linked with the rollback and session information

- XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION

- OBJECT_ID object being locked

- SESSION_ID session id

- ORACLE_USERNAME oracle user name

- OS_USER_NAME OS user name

- PROCESS OS process id

- LOCKED_MODE lock mode

V$RESOURCE list of all the currently locked resources in the system. Each row can be associated with one or more rows in V$LOCK
- TYPE, ID1 and ID2 determine the resource
DBA_DDL_LOCKS has a row for each DDL lock that is being held, and one row for each outstanding request for a DDL lock. It is subset of DBA_LOCKS same description as the DBA_LOCK view
DBA_WAITERS view that retrieve information for each session waiting on a lock (created via $ORACLE_HOME/rdbms/admin/catblock.sql)

- WAITING_SESSION waiting session

- HOLDING_SESSION holding session

- LOCK_TYPE, LOCK_ID1, LOCK_ID2 resource locked

- MODE_HELD lock type held

- MODE_REQUESTED lock type requested

DBA_BLOCKERS view that gives the blocking sessions (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
-HOLDING_SESSION holding session

Oracle data dictionary views

Oracle dynamic performance views

More Oracle DBA tips, please visit Oracle DBA Tips