Oracle,解除死锁

Posted by Alei Blog on October 25, 2017

场景

  • 数据库可能发生死锁了,重启数据库是最简单粗暴的方案
  • 有时没有服务器的权限,有时甲方使用小型机导致条件严苛,不能重启数据库,只能死磕数据库的锁

排查数据库死锁

  • 查询表锁定
    select c.sid         , c.serial#    , a.oracle_username , a.os_user_name , 
         b.object_name , a.locked_mode, c.machine         , c.event
    from v$locked_object a, dba_objects b, v$session c
     where b.object_id  = a.object_id
     and a.session_id = c.sid
     and a.oracle_username = '数据库用户名大写'
    

数据库级解除表锁定

  • 解除表锁定
    alter system kill session 'sid,serial#'; 
    例如
    alter system kill session '107,4801'; 
    
  • 拼接所有解锁语句,查询后,复制全部结果直接执行
    select 'alter system kill session ''' || c.sid || ',' || c.serial# || ''';' as 要执行的语句,
         a.oracle_username , a.os_user_name , b.object_name , a.locked_mode
    from v$locked_object a, dba_objects b, v$session c
     where b.object_id  = a.object_id
     and a.session_id = c.sid
     and a.oracle_username = '数据库用户名大写';
    
  • 执行后,不是所有的表锁定都能解除,剩下的需要操作系统级释放

操作系统级解除表锁定

  • 查询出spid
    select c.sid, c.serial#, a.oracle_username , a.os_user_name ,
         b.object_name , a.locked_mode, d.spid, c.program
    from v$locked_object a, dba_objects b, v$session c, v$process d
     where b.object_id  = a.object_id
     and a.session_id = c.sid
     and c.paddr=d.addr
     and a.oracle_username = '数据库用户名大写';
    
  • 根据spid,用操作系统的shell,杀死线程
    unix适用
    # kill -9 某个spid
    windows和unix都适用
    orakill sid spid  举例: orakill orcl 12345