oracle中查看和删除被锁表

oracle中查看表是否被锁
查看表是否被锁
SELECT
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
    sid,
    serial#
FROM
    v$session
WHERE
    sid in (SELECT
                a.sid
            FROM
                v$lock a,
                all_objects b
            WHERE
                TYPE = 'TM' AND
                a.id1 = b.object_id
            )