To know the locks held on a particular table
SELECT 'alter system kill session'''||A.SID||','||A.SERIAL#||''' IMMEDIATE;' 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='<tablename>'
order by A.USERNAME;
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='<tablename>'
order by A.USERNAME;
Tags:
Oracle