--
-- locks.sql
-- by Brian Peasland
-- 12 January 2000
-- 
-- This script shows 1) users waiting for locked resources
-- and 2) the users who are locking those who are waiting.
--

PROMPT
PROMPT Users Locked, Waiting For Resources
PROMPT

SELECT b.username,b.serial#,c.sid,c.owner object_owner,c.object,a.sql_text sql
FROM v$access c, v$sqltext a, v$session b
WHERE a.address = b.sql_address AND a.hash_value = b.sql_hash_value
AND b.sid = c.sid AND b.lockwait IS NOT NULL 
AND c.owner NOT IN ('SYS','SYSTEM');

PROMPT
PROMPT Resources They Are Waiting On
PROMPT

SELECT x.sid,x.serial#,x.username,y.id1,z.sql_text sql
FROM v$sqltext z, v$session x, v$lock y
WHERE y.id1 IN 
     (SELECT distinct b.id1
      FROM v$lock b, v$session a
      WHERE b.kaddr = a.lockwait)
AND x.sid = y.sid AND z.hash_value = x.sql_hash_value
AND y.request = 0;