blocker.sql

 select s1.username || '@##@' || s1.machine || ' ( Blocker-SID=' || s1.sid ||
        ' )  is blocking ' || s2.username || '@##@' || s2.machine || ' ( Blocked-SID=' ||
        s2.sid || ' ) ' AS blocking_status
   from v$lock l1, v$session s1, v$lock l2, v$session s2
  where s1.sid = l1.sid
    and s2.sid = l2.sid
    and l1.BLOCK = 1
    and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2;

set lines 200 pages 9999
select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK,
DECODE (BLOCK, 0, '', 'blocker') blocker,
DECODE (request, 0, '', 'waiter') waiter
from gv$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv$lock where request>0)
order by blocker;

select 'ALTER SYSTEM DISCONNECT SESSION '''||sid||','||serial#||''' immediate;'
from v$session
where sid='&sid';

select 'alter system disconnect session '''||a.sid||','||a.serial#||''' immediate;',p.SPID,c.object_name,
b.SESSION_ID,b.ORACLE_USERNAME,b.OS_USER_NAME,a.SID,a.SERIAL#
from v$process p,V$session a,v$locked_object b,all_objects c
where p.ADDR = a.PADDR and a.PROCESS=b.PROCESS and c.object_id=b.OBJECT_ID;