Finding all the locks in Oracle

--- Finding all the locks in the database

set linesize 150
spool lock.out
COLUMN     DT         FORMAT A15
COLUMN     USR         FORMAT A10
col    object_name    format a25
col    object        format a30
COL    SID        FORMAT 9999
COL    SERIAL#        FORMAT 99999
COL    PID        FORMAT A8

SELECT     /*+ rule */ count(1)
FROM     V$SESSION A,   
    V$LOCK B,
    dba_objects c,
    v$process d
WHERE     A.SID=B.SID AND
    b.id1=c.object_id and
    A.USERNAME IS NOT NULL AND
    a.paddr=D.addr;


SELECT     /*+ rule */ A.SID,
    a.serial#,
    d.spid pid,
    A.USERNAME USR,
--    c.object_name,
decode(b.type, 'MR', decode(a.username, null,
                            'DICTIONARY OBJECT', a.username||'.'||c.object_name),
                      'TD', c.object_name,
                      'TM', c.object_name,
                      'RW', 'FILE#='||substr(b.id1,1,3)||
                      ' BLOCK#='||substr(b.id1,4,5)||' ROW='||b.id2,
                      'TX', 'RS+SLOT#'||b.id1||' WRP#'||b.id2,
                      'WL', 'REDO LOG FILE#='||b.id1,
                      'RT', 'THREAD='||b.id1,
                      'TS', decode(b.id2, 0, 'ENQUEUE',
                                             'NEW BLOCK ALLOCATION'),
                      'ID1='||b.id1||' ID2='||b.id2) object,
decode(b.type,
          -- Long locks
                      'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',
                      'UL', 'PLS USR LOCK',
          -- Short locks
                      'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',
                      'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',
                      'CU', 'CURSOR BIND ',
                      'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',
                      'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',
                      'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',
                      'FI', 'SGA OPN FILE',
                      'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',
                      'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',
                      'LS', 'LOG SWITCH  ',
                      'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',
                      'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',
                      'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',
                      'RW', 'ROW WAIT    ',
                      'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',
                      'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',
                      'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',
                      'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',
                      'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',
                      'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',
                      'TYPE='||b.type) type,
    LTRIM(RTRIM(TO_CHAR(A.LOGON_TIME,'DD/MM'))) DT,
    B.CTIME,b.block
FROM     V$SESSION A,   
    V$LOCK B,
    dba_objects c,
    v$process d
WHERE     A.SID=B.SID AND
    b.id1=c.object_id and
    A.USERNAME IS NOT NULL AND
    a.paddr=D.addr
order    by a.sid,b.type
/
spool off

--Finding the locks on a particular table/object




set linesize 150
COLUMN     DT         FORMAT A15
COLUMN     USR         FORMAT A10
col    object_name    format a25
col    object        format a30
COL    SID        FORMAT 9999
COL    SERIAL#        FORMAT 99999
COL    PID        FORMAT A10

SELECT     'alter system kill session'''||A.SID||','||A.SERIAL#||''';'  KILLSTMT,
    'kill -9 '||D.spid ,
    A.SID,
    a.serial#,A.INST_ID,
    d.spid pid,
    A.USERNAME USR,
--    c.object_name,
decode(b.type, 'MR', decode(a.username, null,
                            'DICTIONARY OBJECT', a.username||'.'||c.object_name),
                      'TD', c.object_name,
                      'TM', c.object_name,
                      'RW', 'FILE#='||substr(b.id1,1,3)||
                      ' BLOCK#='||substr(b.id1,4,5)||' ROW='||b.id2,
                      'TX', 'RS+SLOT#'||b.id1||' WRP#'||b.id2,
                      'WL', 'REDO LOG FILE#='||b.id1,
                      'RT', 'THREAD='||b.id1,
                      'TS', decode(b.id2, 0, 'ENQUEUE',
                                             'NEW BLOCK ALLOCATION'),
                      'ID1='||b.id1||' ID2='||b.id2) object,
decode(b.type,
          -- Long locks
                      'TM', 'DML/DATA ENQ',   'TX', 'TRANSAC ENQ',
                      'UL', 'PLS USR LOCK',
          -- Short locks
                      'BL', 'BUF HASH TBL',  'CF', 'CONTROL FILE',
                      'CI', 'CROSS INST F',  'DF', 'DATA FILE   ',
                      'CU', 'CURSOR BIND ',
                      'DL', 'DIRECT LOAD ',  'DM', 'MOUNT/STRTUP',
                      'DR', 'RECO LOCK   ',  'DX', 'DISTRIB TRAN',
                      'FS', 'FILE SET    ',  'IN', 'INSTANCE NUM',
                      'FI', 'SGA OPN FILE',
                      'IR', 'INSTCE RECVR',  'IS', 'GET STATE   ',
                      'IV', 'LIBCACHE INV',  'KK', 'LOG SW KICK ',
                      'LS', 'LOG SWITCH  ',
                      'MM', 'MOUNT DEF   ',  'MR', 'MEDIA RECVRY',
                      'PF', 'PWFILE ENQ  ',  'PR', 'PROCESS STRT',
                      'RT', 'REDO THREAD ',  'SC', 'SCN ENQ     ',
                      'RW', 'ROW WAIT    ',
                      'SM', 'SMON LOCK   ',  'SN', 'SEQNO INSTCE',
                      'SQ', 'SEQNO ENQ   ',  'ST', 'SPACE TRANSC',
                      'SV', 'SEQNO VALUE ',  'TA', 'GENERIC ENQ ',
                      'TD', 'DLL ENQ     ',  'TE', 'EXTEND SEG  ',
                      'TS', 'TEMP SEGMENT',  'TT', 'TEMP TABLE  ',
                      'UN', 'USER NAME   ',  'WL', 'WRITE REDO  ',
                      'TYPE='||b.type) type,
    LTRIM(RTRIM(TO_CHAR(A.LOGON_TIME,'DD/MM'))) DT,
    B.CTIME,b.block
FROM     GV$SESSION A,   
    GV$LOCK B,
    dba_objects c,
    Gv$process d
WHERE     A.SID=B.SID AND
    b.id1=c.object_id and
    A.USERNAME IS NOT NULL AND
    a.paddr=D.addr
    and c.object_name='&OBJ'
order    by A.USERNAME
/