RE: Locking

> -----Original Message-----
> From: Richard Lau [mailto:xsn0_at_hotmail.com]
>
> Recently, one of my users nor I could update a table.  As I
> dug around, I
> couldn't find any of the 'lock' tables such as dba_locks. 
> Eventually, I
> managed to start data gatherer and get OEM's lock manager to
> kill the hung
> session.  What do you guys do to find and terminate this
> deadlock or hung
> sessions?  Are the 'lock' system tables built with specific
> options during
> db build?

To create the "dba_locks" view, run
$ORACLE_HOME/rdbms/admin/catblock.sql

Here is a sample SQL*Plus script to find locks in the database. This originally came from a list member (very possibly Jared Still).

column username format a10 heading "User" column object format a20 heading "Object" column type format a4 heading "Type"
column mode_desc format a15 heading "Mode"

/* only non-sys objects */
select

   s.username,
   s.sid,
   o.owner || '.' || o.object_name as object,
   l.type,
   l.request,
   l.lmode,
   decode (l.lmode, 1, '',
                    2, 'Row Share',
                    3, 'Row exclusive',
                    4, 'Share',
                    5, 'Share row exclusive',
                    6, 'Exclusive',
                    'Other')
    as mode_desc
 from
   v$lock l,
   v$session s,
   dba_objects o
 where
   l.sid = s.sid
   and l.id1 = o.object_id
   and o.owner != 'SYS' ;