> -----Original Message-----
> From: Richard Lau [mailto:[EMAIL PROTECTED]]
>
> 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' ;
