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%';