lock.sql

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;