网上有很多这样的文章,我希望的是迅速找到一张表的所有的行级锁,并把它们一并处理掉。所以改进了一下:
要是想用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是没有意义的,因为多数会受连接池的影响,导致这个时间失去意义。