set pages 1000;
set lines 160;
set feedback off;
col BLOCKER for a20
col BLOCKEE for a20
col module for a20
col program for a20
select
(select username from gv$session where sid=a.sid and inst_id=a.inst_id) blocker,a.sid,
(select module from gv$session where sid=a.sid and inst_id=a.inst_id) "Program",
(select inst_id from gv$session where sid=a.sid and inst_id=a.inst_id) "Instance",
(select status from gv$session where sid=a.sid and inst_id=a.inst_id) "Status",
(select last_call_et/3600 from gv$session where sid=a.sid and inst_id=a.inst_id) "Inactive Since",
'is blocking',
(select username from gv$session where sid=b.sid and inst_id=b.inst_id) blockee,b.sid,
(select module from gv$session where sid=b.sid and inst_id=b.inst_id) "Program",
(select inst_id from gv$session where sid=b.sid and inst_id=b.inst_id) "Instance",
(select status from gv$session where sid=b.sid and inst_id=b.inst_id) "Status" ,
(select last_call_et/3600 from gv$session where sid=b.sid and inst_id=b.inst_id) "Inactive Since"
from gv$lock a,gv$lock b
where a.block=1
and b.request > 0
and a.id1=b.id1
and a.id2=b.id2
/
