Locking Tree script

---*****************************

---*   Locking Tree            *

---*  By Eini Trujillo. Vzla   *

---*  April 2013               *

---*****************************

set echo off

set feed off
column tm new_value file_time noprint

column tbd new_value bd_name noprint

set lin 180

select to_char(sysdate, 'YYYYMMDDHH24MISS') tm from dual ;
prompt http://studioracle.blogspot.com/2014/05/locking-tree-script.html

select instance_name tbd from v$instance;

spool locktree..&bd_name..&file_time..log

set lin 180

col Bloqueos for a100

SELECT  LPAD(' ',(LEVEL-1)*10) ||   LPAD(A.Bloqueando,10,'-') ||  LPAD(A.Bloqueado,10,'-') Locks
FROM
(select a.sid Bloqueando, b.sid Bloqueado
from v$lock a, v$lock b
where a.block in (1,2) and b.request >0 and a.id1=b.id1 and a.id2=b.id2) A
CONNECT BY PRIOR a.Bloqueado = A.Bloqueando
START WITH A.Bloqueando in (select  sid from
 v$lock  l2 where block in (1,2) and type='TX' and not exists (
  select  1 from v$lock l1 where l1.request>0  and l1.type='TX' and l1.sid=l2.sid));

spool off;