Find Oracle waiting sessions with DBA_WAITERS
March 31, 2010 15:14:39 Last update: March 31, 2010 15:17:46
DBA_WAITERS shows all the sessions that are waiting for a lock.
Or join the v$session view to find out who it is:
| Column | Datatype | 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 |
SQL> set lin 120
SQL> select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD
--------------- --------------- -------------------------- ----------------------------------------
MODE_REQUESTED LOCK_ID1 LOCK_ID2
---------------------------------------- ---------- ----------
235 364 Transaction Exclusive
Exclusive 196617 151100
SQL>
Or join the v$session view to find out who it is:
select w.waiting_session, w.mode_requested, s.username, s.machine, s.osuser from v$session s, dba_waiters w where s.sid = w.holding_session;
