ORACLE查LOCK表

oracle中檢視錶是否被鎖
檢視錶是否被鎖
SELECT /*+ rule*/
a.sid, b.owner, object_name, object_type
FROM v$lock a, all_objects b
WHERE TYPE = 'TM'
and a.id1 = b.object_id;

查到的都是被鎖的表

這樣可以把它殺掉

SELECT sid,serial# FROM v$session WHERE sid = &sid;
alter system kill session ‘sid,serial#’;

要想查到更詳細的資訊

select a.sid, b.serial#, b.username, b.osuser, b.machine, b.program, c.object_name,d.spid ,
decode(a.type,
'mr', 'media recovery',
'rt','redo thread',
'un','user name',
'tx', 'transaction',
'tm', 'dml',
'ul', 'pl/sql user lock',
'dx', 'distributed xaction',
'cf', 'control file',
'is', 'instance state',
'fs', 'file set',
'ir', 'instance recovery',
'st', 'disk space transaction',
'ts', 'temp segment',
'iv', 'library cache invalida-tion',
'ls', 'log start or switch',
'rw', 'row wait',
'sq', 'sequence number',
'te', 'extend table',
'tt', 'temp table',
'unknown') locktype,
decode(a.lmode,
0, 'none',
1, 'null',
2, 'row-s',
3, 'row-x',
4, 'share',
5, 's/row-x',
6, 'exclusive', 'unknown') lockmode
from v$lock a, v$session b, all_objects c, v$process d
where a.sid = b.sid
and a.type in ('TM','TX')
and c.object_id = a.id1
and b.paddr = d.addr;

[@more@]