查询数据库中锁的持有者和被阻塞者的SQL
朋友介绍的代码,感觉不错,与大家分享
SELECT
s1.username "WAITING USER"
, s1.osuser "OS User"
, w.session_id "Sid"
, p1.spid "PID"
, s2.username "HOLDING User"
, s2.osuser "OS User"
, h.session_id "Sid"
, p2.spid "PID"
FROM
sys.v_$process p1
, sys.v_$process p2
, sys.v_$session s1
, sys.v_$session s2
, dba_locks w
, dba_locks h
WHERE
h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND w.lock_type (+) = h.lock_type
AND w.lock_id1 (+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
/
朋友介绍的代码,感觉不错,与大家分享
SELECT
s1.username "WAITING USER"
, s1.osuser "OS User"
, w.session_id "Sid"
, p1.spid "PID"
, s2.username "HOLDING User"
, s2.osuser "OS User"
, h.session_id "Sid"
, p2.spid "PID"
FROM
sys.v_$process p1
, sys.v_$process p2
, sys.v_$session s1
, sys.v_$session s2
, dba_locks w
, dba_locks h
WHERE
h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND w.lock_type (+) = h.lock_type
AND w.lock_id1 (+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
/