Oracle查询锁表和解锁

Oracle数据库操作中，我们有时会用到锁表查询以及解锁和kill进程等操作

• 锁表查询的代码有以下的形式：

    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; 

• 杀掉对应进程

    alter system kill session'1025,41';

其中1025为sid,41为serial#.

快速查询和kill

    --查锁表进程
    select sess.sid, 
  sess.serial#, 
  lo.oracle_username, 
  lo.os_user_name, 
  ao.object_name, 
  lo.locked_mode 
  from v$locked_object lo, 
  dba_objects ao, 
  v$session sess 
    where ao.object_id = lo.object_id and lo.session_id = sess.sid; 
    select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID; 

   --杀锁表进程
   alter system kill session '1383,52821';