oracle 锁表 解锁 批量解锁

oracle锁表情况时有发生，本文介绍了查看具体锁信息、强制删除锁、批量解锁等操作

一. 查看锁信息的方式

1. 锁表查询：

select count(*) from v$locked_object;
select * from v$locked_object;

2. 查询具体被锁的表信息：

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;

3. 查询引起锁表的语句：

select l.session_id sid, 
       s.serial#, 
       l.locked_mode, 
       l.oracle_username, 
       s.user#, 
       l.os_user_name, 
       s.machine, 
       s.terminal, 
       a.sql_text, 
       a.action 
  from v$sqlarea a, v$session s, v$locked_object l 
where l.session_id = s.sid 
   and s.prev_sql_addr = a.address 
order by sid, s.serial#;

4.查看是哪个session引起的：

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

二. 解锁方式

1. 直接杀掉对应进程

alter system kill session'1025,41';

其中1025为sid,41为serial#，均可以使用上面第四条的语句进行查询出来

2. 批量解锁的存储过程

declare cursor mycur is  
select b.sid,b.serial#  
　　from v$locked_object a,v$session b  
　　where a.session_id = b.sid group by b.sid,b.serial#;  
　　
begin  
  for cur in mycur  
    loop    
     execute immediate ( 'alter system  kill session  '''||cur.sid || ','|| cur.SERIAL# ||''' ');  
     end loop;  
     
end;