oracle中锁的查询

在工作中整理了一下自己常用的脚本，将它分享出来。

WITH LOCK_1 AS ( SELECT DISTINCT S.INST_ID, S.SID, S.BLOCKING_SESSION, S.LAST_CALL_ET FROM GV$SESSION S WHERE S.BLOCKING_SESSION IS NOT NULL and BLOCKING_SESSION_STATUS = 'VALID' ) SELECT DISTINCT BLOCKING_SESSION FROM LOCK_1 WHERE BLOCKING_SESSION NOT IN ( SELECT SID FROM LOCK_1);

--集群索起 select b.INST_ID,A.sid, b.serial#, 'ALTER SYSTEM KILL SESSION '||''''||b.SID||','||b.SERIAL#||',@'||b.inst_id||''''||' IMMEDIATE;' killuser, listagg (c.OBJECT_NAME, ',') WITHIN GROUP (ORDER BY c.OBJECT_NAME) lck from gv$lock a,gv$session b,all_objects c,gV$PROCESS D where a.sid=b.sid and a.INST_ID=b.INST_ID and a.INST_ID=d.INST_ID and b.INST_ID=d.INST_ID and a.type in ('TM','TX') and c.object_id=a.id1 AND B.PADDR=D.ADDR group by b.INST_ID,A.sid, b.serial#, 'ALTER SYSTEM KILL SESSION '||''''||b.SID||','||b.SERIAL#||',@'||b.inst_id||''''||' IMMEDIATE;';

--谁阻塞了谁 SELECT L1.SID, ' IS BLOCKING ', L2.SID FROM V$LOCK L1, V$LOCK L2 WHERE L1.BLOCK = 1 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1; 查询会话阻塞情况，某个会话阻塞了多少个会话。 select count(*),blocking_session from v$session where blocking_session is not null group by blocking_session; 查询数据库中的锁 select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3; --什么对象被锁了 select b.username,b.sid,b.serial#,logon_time,(select object_name from dba_objects where object_id=a.object_id) 锁定的对象, decode(locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', 'Unknown') 锁模式 from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time; alter system kill session '3404,47431,@1' immediate; alter system kill session '3404,44829,@2' immediate; --查询对应表的锁 select do.object_name, do.object_type, gv.inst_id, gv.sid, gv.serial#, (Select max(sql_text) From gv$sql s Where s.Sql_id=gv.sql_id and s.INST_ID=gv.INST_ID and s.hash_value =gv.sql_hash_value and s.address=gv.sql_address)stext, gv.program, gv.machine, gv.SQL_ID, gv.EVENT, gv.WAIT_CLASS, gv.WAIT_TIME, gv.PADDR from gv$locked_object gl, gv$session gv, dba_objects do where gl.SESSION_ID = gv.SID and gl.OBJECT_ID = do.object_id and wait_class<>'Idle'; --and do.object_name like 'WRH%';