发现网上很多锁检查脚本更新一个锁检查脚本:都有些不符合预期,自己写了一个:
set line 300
col "SQL" for a50
col "waiting sid" for 99999
col event for a35
col "Holder SQL" for a50
SELECT DISTINCT a.SID "waiting sid",---被阻塞会话SID
c.sql_text "SQL",---被阻塞会话执行sql
b.SID "blocking sid",---阻塞源会话SID
a.blocking_instance,---阻塞源实例id
b.serial#,---阻塞源会话serial#
b.event,---阻塞源等待时间
b.sql_id,---阻塞源sqlid
b.prev_sql_id,---阻塞源prev_sqlid
d.sql_text "Holder SQL" ---阻塞源SQL
FROM gv$session a,
v$session b,
gv$sql c,
v$sql d
WHERE a.blocking_session = b.SID
AND c.sql_id = a.sql_id
and b.blocking_session is null
AND d.sql_id = NVL (b.sql_id, b.prev_sql_id);