oracle运用(三) oracle数据库解锁

1.假如oracle数据库被锁,解锁脚本:

--查询语句
select count(*) from v$locked_object;
select * from v$locked_object;

--查看哪个表被锁

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;

--查看是哪个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;

--kill 
--sid
--serial
alter system kill session 'sid,serial';

2.批量解锁

--锁表查询SQLSELECT object_name, machine, s.sid, s.serial#                  
SELECT object_name, machine, s.sid, s.serial# 
FROM gv$locked_object l, dba_objects o, gv$session s 
WHERE l.object_id　= o.object_id 


--释放SESSION SQL: 
--alter system kill session 'sid, serial#'; 

 ALTER system kill session '186, 18150'; 
 
 --批量解除表锁定
 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;