oracle查询锁表解锁语句

数据库  /  管理员 发布于 2年前   234

一.查询锁定的表

如下语句，都可以查询锁定的表

语句一：

select a.sid,       a.serial#,       p.spid,       c.object_name,       b.session_id,       b.oracle_username,       b.os_user_name  from v$process p, v$session a, v$locked_object b, all_objects c where p.addr = a.paddr   and a.process = b.process   and c.object_id = b.object_id   and c.OBJECT_name = 'TABLE_NAME';

语句二：

SELECT s.sid, s.serial#, o.object_name, s.machine  FROM gv$locked_object l, dba_objects o, gv$session s WHERE l.object_id　 = o.object_id   AND l.session_id = s.sid   AND o.object_name = 'TABLE_NAME';

语句三：

SELECT l.session_id sid,       s.serial#,       l.locked_mode,       l.oracle_username,       l.os_user_name,       s.machine,       s.terminal,       o.object_name,       s.logon_time  FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id   AND l.session_id = s.sid   AND o.object_name = 'TABLE_NAME' ORDER BY sid, s.serial#;

语句四：

select a.sid,       b.serial#,       c.object_name,       decode(a.lmode,              0,              'None',              1,              'Null',              2,              'Row-S',              3,              'Row-X',              4,              'Share',              5,              'S/Row-X',              6,              'Exclusive',              'Unknown') LockMode,       B.MACHINE,       D.SPID,       b.PROGRAM  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   and c.object_name = 'TABLE_NAME';

 

二.kill掉锁住的进程，解锁

--释放SESSION SQLalter system kill session 'SID,serial#';