blocking_chain3

Col username for a12
Col MACHINE for a17
Col OSUSER for a15
Col PROCESS for a12
select s1.inst_id inst, 'Holder' status, s1.sid, s1.serial#, s1.sql_id, s1.username, s1.machine, s1.osuser, decode(instr(s1.process,'@'),0,s1.process,substr(s1.process,1,INSTR(s1.process,'@')-1)) process,
-- s2.username,
-- s2.machine,
s2.inst_id blocked_inst, s2.sid blocked_sid, s2.serial# blocked_serial#
-- s1.username || '@' || s1.machine ||
-- ' ( exec att_appsdba.kill_session(' || s1.sid ||','||s1.serial#|| ' )  is blocking INST ' ||
-- s1.INST_ID || '@' ||s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK > 0 and l2.request > 0 and l2.ctime >= 450 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id;