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;

select /*+rule*/
 decode(l.lmode, 0, '<==waiter', 6, '==>holder') identifier,
 s.status,
 s.sql_id,
 s.logon_time,
 s.module,
 s.sid,
 s.serial#,
 s.inst_id,
 p.spid,s.SQL_EXEC_ID,
 'alter system kill session ''' || s.sid || ',' || s.serial# || ',@'|| s.INST_ID || ''';' kill_a,
 decode(l.lmode,
        0,
        'None',
        1,
        'Null',
        2,
        'Row-x (SX)',
        4,
        'Share',
        5,
        'S/Row-X (SSX)',
        6,
        'Exclusive',
        to_char(l.lmode)) hold,
 decode(l.request,
        0,
        'None',
        1,
        'Null',
        2,
        'Row-x (SX)',
        4,
        'Share',
        5,
        'S/Row-X (SSX)',
        6,
        'Exclusive',
        to_char(l.request)) requested,
 to_char(l.id1) lock_id1,
 to_char(l.id2) lock_id2,
 l.ctime,
 s.row_wait_obj#,
 s.process,
 s.action,
 decode(l.type,
        'CF',
        'Control File',
        'DX',
        'Distributed Xaction',
        'FS',
        'File Set',
        'IR',
        'Instance Recovery',
        'IS',
        'Instance State',
        'IV',
        'Library Cache Invalidation',
        'LS',
        'Log Start or Switch',
        'MR',
        'Media Recovery',
        'RT',
        'Redo Thread',
        'RW',
        'Row Wait',
        'SQ',
        'Sequence Number',
        'ST',
        'Disk Space Transaction',
        'TE',
        'Extend Table',
        'TM',
        'DML',
        'TS',
        'Temp Table',
        'TX',
        'Transaction',
        'UL',
        'PL/SQL User Lock',
        'UN',
        'User Name',
        l.type) Lock_type,
 s.program,
 s.command,
 s.terminal,
 s.machine,
 s.client_info,
 s.logon_time,
 s.last_call_et,
 s.client_identifier,
 s.username,
 s.osuser,
 s.schemaname,
 s.paddr
  from gv$lock l, gv$session s, gv$process p
 where (l.id1, l.id2, l.type) in
       (select /*+ no_merge*/
         gl.id1, gl.id2, gl.type
          from gv$lock gl
         where request != 0)
   and l.sid = s.sid
   and l.inst_id = s.inst_id
   and s.paddr = p.addr(+)
   and s.inst_id = p.inst_id(+)
 order by 1 desc, l.id1;

set lines 200 pages 50000
select distinct 'ALTER SYSTEM DISCONNECT SESSION  ''' || a.sid ||','||a.SERIAL# || ',@' || a.INST_ID || ''' immediate;'
from gv$process p,gV$session a,gv$locked_object b,all_objects c
where p.ADDR = a.PADDR and a.PROCESS=b.PROCESS and c.object_id=b.OBJECT_ID and c.object_name in upper('CUX_WS_INVOKE_LOG');

set lines 200 pages 50000
Select 'ALTER SYSTEM DISCONNECT SESSION  ''' || b.sid ||','||b.SERIAL# || ',@' || b.INST_ID || ''' immediate;'
From dba_ddl_locks a, gv$session b
Where a.session_id=b.SID
And a.name =upper('CUX_CM_ZG_SY_PKG') and b.username=upper('CM');

select a.Object_Name, d.Spid Pid, c.sid, c.SERIAL#, c.ACTION, c.CLIENT_IDENTIFIER, b.INST_ID, 'alter system kill session '''||c.sid||','||c.serial#||',@'||b.inst_id||''';'
from Dba_Objects a, gV$locked_Object b, gV$session c, gV$process d
where a.Object_Id = b.Object_Id
and c.Paddr = d.Addr
and c.INST_ID = d.INST_ID
and b.Session_Id = c.Sid
and b.INST_ID = c.INST_ID
and a.Object_Name like '%GL_JE_HEADERS%'
--and c.CLIENT_IDENTIFIER like '%XUWANXIN%'
--and action like '%WANG%'