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
/
