rem ********************************************************************
rem * Filename          : locks.sql - Version 1.0
rem * Author            : Virag Saksena
rem * Original          : 20-MAR-95
rem * Description       : Information about sessions waiting for locks
rem * Usage             : start locks.sql
rem * Updates           : 29-JAN-96
rem *                   : 02-MAY-00 SE  Increased length of ssid format.
rem *                   :               Added clear columns statements. 
rem ********************************************************************

CLEAR COLUMNS

col ssid    form A7
col lmode   form 999 heading "loc"
col request form 999 heading "req"
col name    form A30

break on id1 on sid

select lpad(' ',decode(a.request,0,0,3))||a.sid ssid,
       a.id1,
       a.lmode,
       a.request,
       c.name
  from sys.obj$  c,
       v$lock    b,
       v$lock    a
 where a.id1 in ( select id1 from v$lock where lmode = 0 )
   and a.sid  = b.sid
   and c.obj# = b.id1
   and b.type = 'TM'
 order by a.id1, a.request, b.sid, c.name
/

CLEAR COLUMNS