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
