Oracle数据库 监看lock script

Script:

SELECT

SUBSTR(s1.username,1,12) "WAITING USER"

, SUBSTR(s1.osuser,1,8) "OS User"

, SUBSTR(TO_CHAR(w.session_id),1,5) "Sid"

, p1.spid "PID"

, SUBSTR(s2.username,1,12) "HOLDING User"

, SUBSTR(s2.osuser,1,8) "OS User"

, SUBSTR(TO_CHAR(h.session_id),1,5) "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 (+)

/

SQL @lock.sql

WAITING USER OS User Sid PID

------------------------ ---------------- ---------- ------------

HOLDING User OS User Sid PID

------------------------ ---------------- ---------- ------------

SYS jack 11 25674

SYS Oracle 17 25726

可以看出，jack user正在等待oracle hold住的锁

只要oracle user commit/rollback，锁就会解开