v$lock:视图列出当前系统持有的或正在申请的所有锁的情况

v$locked_object:视图列出当前系统中哪些对象正被锁定

1)、查询当前数据库锁的情况,以及导致锁的sql语句:

Select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号,sq.SQL_TEXT
From v$locked_object l , dba_objects o , v$session s , v$process p,v$sqlarea sq
Where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr and s.SQL_ADDRESS = sq.ADDRESS;

2)、杀掉非正常的一些锁

alter system kill session 'sid,serial#';

3)、批量杀掉未释放的锁

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;  

  

posted on 2014-01-18 15:25  壊壊—娚孩  阅读(445)  评论(0编辑  收藏  举报