set verify off feedback off
insert into lock_holders
select &1,r.pid, p.username, r.type, r.id1, r.id2, r.request req, h.pid hpid, h.lmode hmod
from all_locks_view h, all_locks_view r,v$process p
where r.request > 1 /* R is waiting for (requesting) the lock */
and h.lmode > 1 /* H is holding the lock */
and h.type = r.type /* H is holding the lock R is requesting */
and r.pid = p.pid /* H is process P */
and h.id1 = r.id1
and h.id2 = r.id2
union
select unique
&1,h.pid, p.username, 'NONE', '0', '0', 0, -1, 0
from all_locks_view h, all_locks_view r, v$process p
where r.request > 1 /* R is waiting for (requesting) the lock */
and h.lmode > 1 /* h is holding the lock */
and h.type = r.type /* H is holding the lock R is requesting */
and h.id1 = r.id1
and h.id2 = r.id2
and h.pid = p.pid /* H is process P */
and p.lockwait is null /* P is not waiting for a lock */
and p.latchwait is null;
column c1 format a25 heading "Process waiting"
column c2 format a4 heading "Type"
column c3 format a14 heading "Mode requested"
column c4 format a14 heading "Mode held"
column c5 format a8 heading "Lock ID1"
column c6 format a8 heading "Lock ID2"
/* Print out the result in a tree structured fashion */
select lpad(' ',2*(level-1))||username||'('||pid||')' c1,
type c2,
decode( req,
0, 'None', /* Mon Lock equivalent */
1, 'Null Mode', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share (S)', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive (X)', /* X */
req) c3,
decode( hmod,
0, 'None', /* Mon Lock equivalent */
1, 'Null Mode', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share (S)', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive (X)', /* X */
hmod) c4,
id1 c5, id2 c6
from lock_holders
where key = &1
connect by prior pid = hpid
start with hpid = -1;
rollback
/
