Oracle数据锁快速查杀

 网上有很多这样的文章，我希望的是迅速找到一张表的所有的行级锁，并把它们一并处理掉。所以改进了一下：

 

要是想用kill -9 sessionPid的方式就会用到视图V$PROCESS，但这样查询速度很慢。

于是可以先建表：

 

/*可以先建表
 create table b_session_process nologging as
 select * from V$PROCESS D
 where 1=0;
 */
 truncate table b_session_process;
 
 insert into b_session_process 
 select * from V$PROCESS D;
 commit;
--查看数据锁
select d.SPID,--操作系统进程号
       c.object_name,
       a.CTIME,--处于当前模式的时间(秒) ,当前模式是指：已经获得锁，或者正在等待锁。
       b.STATUS,--会话的状态（ACTIVE/INACTIVE/Killed）。ACTIVE：sql正在执行（可能是执行时间长，也可能是正在等待锁）。--INACTIVE：未活动的会话，等待有sql进入会话以执行（没有sql正在执行，很可能是异常会话:已经获得锁，并且未释放。）。---Killed：被标注为删除
       A.sid,
       b.serial#,
       decode(A.type,'MR','MediaReco-very','RT','RedoThread','UN','UserName','TX','Transaction','TM','DML','UL','PL/SQLUserLock','DX','DistributedXaction','CF','ControlFile','IS','InstanceState','FS','FileSet',-'IR','InstanceRecovery','ST','DiskSpaceTransaction','TS','TempSegment','IV','LibraryCacheInvalida-tion','LS','LogStartorSwitch','RW','RowWait','SQ','SequenceNumber','TE','ExtendTable','TT','TempTable','Unknown') LockType,
       b.username,
       decode(a.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Exclusive','Unknown') LockMode,
       b.LOGON_TIME,
       B.MACHINE,
       b.PADDR
  from v$lock a, all_objects c, v$session b ,
  --V$PROCESS D
  b_session_process D 
  where 1 = 1
   and d.ADDR=b.PADDR
   and a.sid = b.sid
   and a.type in ('TM', 'TX')
   and a.id1 = c.object_id
   and c.OBJECT_TYPE='TABLE'--大多数锁都是锁定表。
   --and c.object_name = 'LOCKED_TABLE'
   --and a.CTIME > 120 --锁定或者等待时间大于120秒
   and b.STATUS='INACTIVE'--长时间占有锁并且不执行sql的会话，应该被杀掉。
   order by a.CTIME desc
   ;

 

 

 

以上的sql加上条件：and c.object_name = 'LOCKED_TABLE' 能在2秒内找到你想要的数据锁所属的session的操作系统进程号。不加这个条件时间就长了，有可能需要2分钟。

 其中LOCKED_TABLE是指要查询的锁所在的表名。

 

找到不活的且长时间占有锁的进程号后。(我认为：不活动的不可能是等待锁的会话。反之，等待锁的会话应该是正在执行中的活动会话)

直接kill -9 $spid

 

 网上有说用这个方式：alter system kill session 'sid,serial#'，我觉得不适合我，主要是这句耗时太长。如果只杀一个重要的锁还可以。

 

 

附上查询数据的所有者sql，可能对某些人有用：

 

 

 --查询数据锁所属
 select command_type,
       sql_text,
       sharable_mem,
       persistent_mem,
       runtime_mem,
       sorts,
       version_count,
       loaded_versions,
       open_versions,
       users_opening,
       executions,
       users_executing,
       loads,
       first_load_time,
       invalidations,
       parse_calls,
       disk_reads,
       buffer_gets,
       rows_processed,
       sysdate start_time,
       sysdate finish_time,
       address sql_address,
       'N' status
  from v$sqlarea
 where address = (select sql_address from v$session where sid = $sid);
--$sid是上一个sql查询的a.sid
 

 

 

 

 

对了以上sql我是在oracle9里面使用的。

 

 

 

注误区：

1.v$locked_object的SESSION_ID并不是持有锁的sessionId，也包括等待该锁的sessionId。

2.v$session的LOGON_TIME是没有意义的，因为多数会受连接池的影响，导致这个时间失去意义。