rem Name:    waiters.sql
rem Purpose: List users waiting for locks
rem Usage:   @waiters
rem Subject: tuning
rem Attrib:  sql
rem Notes:   Requires catblock.sql to be executed as SYS

@setup

column waiting format a10
column waiting_session format 990 heading "WID"
column holding format a10
column holding_session format 990 heading "HID"
column mode_requested format a10
column mode_held format a10
column lock_type format a12
column lock_id1 format a8

select
s1.username waiting
,w.waiting_session
,w.mode_requested
,s2.username holding
,w.holding_session
,w.mode_held
,w.lock_type
,w.lock_id1
from sys.dba_waiters w, v$session s1, v$session s2
where w.waiting_session = s1.sid(+)
and w.holding_session = s2.sid(+)
;

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips