select /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
use_nl(O)
use_nl(U)
*/
/* first the table-level locks (TM) and mixed TM/TX TX/TM */
S_LOCKER.SID SID_LOCKER,
S_LOCKER.SERIAL# SERIAL#_LOCKER,
S_LOCKER.USERNAME USERNAME_LOCKER,
S_LOCKER.OSUSER OSUSER_LOCKER,
S_LOCKER.MODULE MODULE_LOCKER,
S_LOCKER.ACTION ACTION_LOCKER,
S_LOCKER.PROGRAM PROGRAM_LOCKER,
S_WAITER.SECONDS_IN_WAIT SECONDS_IN_WAIT,
S_WAITER.SID SID_WAITER,
S_WAITER.SERIAL# SERIAL#_WAITER,
S_WAITER.USERNAME USERNAME_WAITER,
S_WAITER.OSUSER OSUSER_WAITER,
S_WAITER.MODULE MODULE_WAITER,
S_WAITER.ACTION ACTION_WAITER,
S_WAITER.PROGRAM PROGRAM_WAITER,
'Table lock (TM): '||O.OWNER||'.'||O.OBJECT_NAME||
' - Mode held: '||
decode(L_LOCKER.LMODE,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: '||to_char(L_LOCKER.LMODE))||
' / Mode requested: '||
decode(L_WAITER.REQUEST,
0, 'None', /* same as Monitor */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
'???: '||to_char(L_WAITER.REQUEST))
SQL_TEXT_WAITER
from
V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
dba_objects O
where S_WAITER.SID = L_WAITER.SID
and L_WAITER.TYPE IN ('TM','TX')
and S_LOCKER.sid = L_LOCKER.sid
and L_LOCKER.ID1 = L_WAITER.ID1
and L_WAITER.REQUEST > 0
and L_LOCKER.LMODE > 0
and L_WAITER.ADDR != L_LOCKER.ADDR
and L_WAITER.ID1 = O.OBJECT_ID
union all
select /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
no_merge(L1_WAITER) use_hash(L1_WAITER)
no_merge(O) use_hash(O)
*/
/* now the (usual) row-locks TX */
S_LOCKER.SID SID_LOCKER,
S_LOCKER.SERIAL# SERIAL#_LOCKER,
S_LOCKER.USERNAME USERNAME_LOCKER,
S_LOCKER.OSUSER OSUSER_LOCKER,
S_LOCKER.MODULE MODULE_LOCKER,
S_LOCKER.ACTION ACTION_LOCKER,
S_LOCKER.PROGRAM PROGRAM_LOCKER,
S_WAITER.SECONDS_IN_WAIT SECONDS_IN_WAIT,
S_WAITER.SID SID_WAITER,
S_WAITER.SERIAL# SERIAL#_WAITER,
S_WAITER.USERNAME USERNAME_WAITER,
S_WAITER.OSUSER OSUSER_WAITER,
S_WAITER.MODULE MODULE_WAITER,
S_WAITER.ACTION ACTION_WAITER,
S_WAITER.PROGRAM PROGRAM_WAITER,
'TX: '||O.SQL_TEXT SQL_TEXT_WAITER
from
V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
V$_LOCK L1_WAITER,
V$OPEN_CURSOR O
where S_WAITER.SID = L_WAITER.SID
and L_WAITER.TYPE IN ('TX')
and S_LOCKER.sid = L_LOCKER.sid
and L_LOCKER.ID1 = L_WAITER.ID1
and L_WAITER.REQUEST > 0
and L_LOCKER.LMODE > 0
and L_WAITER.ADDR != L_LOCKER.ADDR
and L1_WAITER.LADDR = L_WAITER.ADDR
and L1_WAITER.KADDR = L_WAITER.KADDR
and L1_WAITER.SADDR = O.SADDR
and O.HASH_VALUE = S_WAITER.SQL_HASH_VALUE;
