Script:
SELECT
SUBSTR(susername) WAITING USER
SUBSTR(sosuser) OS User
SUBSTR(TO_CHAR(wsession_id)) Sid
pspid PID
SUBSTR(susername) HOLDING User
SUBSTR(sosuser) OS User
SUBSTR(TO_CHAR(hsession_id)) Sid
pspid PID
FROM
sysv_$process p
sysv_$process p
sysv_$session s
sysv_$session s
dba_locks w
dba_locks h
WHERE
hmode_held != None
AND hmode_held != Null
AND wmode_requested != None
AND wlock_type (+) = hlock_type
AND wlock_id (+) = hlock_id
AND wlock_id (+) = hlock_id
AND wsession_id = ssid (+)
AND hsession_id = ssid (+)
AND spaddr = paddr (+)
AND spaddr = paddr (+)
/
SQL> @locksql
WAITING USER OS User Sid PID
HOLDING User OS User Sid PID
SYS jack
SYS oracle
可以看出jack user正在等待oracle hold住的锁
只要oracle user commit/rollback锁就会解开
