Re: Locked tables...

Here are some scripts posted earlier that should be able to help you. C/o Tim Mcconechy

See Who is locked...and what they are doing.. select a.username,a.sid,a.serial#,b.id1,c.sql_text from v$session a, v$lock b, v$sqltext c
where a.lockwait = b.kaddr
and a.sql_Address = c.address
and a.sql_hash_value= c.hash_value

Then see who is locking them and what they are doing???

select a.username,a.sid,a.serial#,b.id1,c.sql_text from v$session a, v$lock b, v$sqltext c
where b.id1 in (select distinct e.id1 from v$session d, v$lock e where d.lockwait=e.kaddr)

and a.sid =b.sid
and c.hash_value = a.sql_hash_value
and b.request=0

To kill the blocking session you need to use the sid and the serial#, but you probably already know that :)
Alternatively, you could look at the locking tools in $ORACLE_HOME/rdbms/admin, of which I -from the top of my head- only remember utllockt.sql, dumping the locking tree, but there definitely are more.

Hth,

Sybrand Bakker, Oracle DBA

cmilner wrote in message <3748EBD8.3F30950A_at_connect.com.au>...
>I have a recurring problem in that one table in the database has a row
called RECORD_LOCK. Often, the users phone saying they can't access a record, 'record already in use'. The procedure I go through is loggin off all users, updating all locks to 'N' in the table, and then allow them log back on.
>
>Is there a way to trace back from row level which Unix process is locking
the table? Unfortunately, simple updating the table from 'Y' to 'N' is not sufficient, the process locking table is still there.
>
>Ta for any help
>
>chris
Received on Mon May 24 1999 - 04:12:28 CDT