查看Oracle被锁的表以及如何解锁

–注意权限问题

1.查看是否有被锁的表：

select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id

2.查看是哪个进程锁的

select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time

3.杀掉进程

alter system kill session 'sid,serial#';

4. 组合

select 'alter system kill session '''|| b.sid||','||b.serial#||''';'
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time

——————————————隔离线———————————————————
5. 万能解锁语句
没研究过这是干啥的，但是好使。

CREATE OR REPLACE PROCEDURE DB_KILL_LOCK_CLIENTS AUTHID DEFINER AS
BEGIN
    FOR REC IN
        (select distinct sid,serial#,inst_id from
        (SELECT object_name, machine, s.sid, s.serial#,s.inst_id,lo.lmode,lo.request,lo.ctime
         FROM gv$locked_object l, dba_objects o, gv$session s,gv$lock lo
         WHERE l.object_id = o.object_id
         AND l.session_id = s.sid
         AND l.session_id = lo.sid
         AND s.inst_id=lo.inst_id
         and lo.lmode=6
         and lo.ctime>10
         and s.username not in ('SYS','SYSTEM')
         AND lo.sid in (select final_blocking_session from gv$session where event='enq: TX - row lock contention' and final_blocking_session is not null)))
    LOOP
      execute immediate 'alter system kill session '''|| rec.sid || ', ' || rec.serial# || ',@'||rec.inst_id||''' immediate' ;
    END LOOP;
END DB_KILL_LOCK_CLIENTS;