oracle查看锁表的sql6,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@]