Script:
SELECT
SUBSTR(s
username
)
WAITING USER
SUBSTR(s
osuser
)
OS User
SUBSTR(TO_CHAR(w
session_id)
)
Sid
p
spid
PID
SUBSTR(s
username
)
HOLDING User
SUBSTR(s
osuser
)
OS User
SUBSTR(TO_CHAR(h
session_id)
)
Sid
p
spid
PID
FROM
sys
v_$process p
sys
v_$process p
sys
v_$session s
sys
v_$session s
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_id
(+) = h
lock_id
AND w
lock_id
(+) = h
lock_id
AND w
session_id = s
sid (+)
AND h
session_id = s
sid (+)
AND s
paddr = p
addr (+)
AND s
paddr = p
addr (+)
/
SQL> @lock
sql
WAITING USER OS User Sid PID
HOLDING User OS User Sid PID
SYS jack
SYS oracle
可以看出
jack user正在等待oracle hold住的鎖
只要oracle user commit/rollback
鎖就會解開
From:http://tw.wingwit.com/Article/program/Oracle/201311/17369.html