Re: How to release acquired locks

Check the v$session table for row corresponding to the user whose session has supposedly hung. Use the SID to query the v$lock table. If there are locks held kill the session by giving "alter system kill session 'sid,serial#'. You obviously need to have the DBA role.

The way i check for locks is :
select o.object_name, l.sid, decode(l.mode ...) { check the Admin guide }
from obj$ o, v$lock l
where l.id1 = o.obj#
or l.id2 = o.obj#
and l.sid = the sid of the user from v$session. ( i neither 've the manuals handy nor 've sql+ running. You probably might need to join sys.dba_objects with obj$ to get the object_name )

If after killing the session, the status for that row remains killed for a long time, you might need to identify the OS process id from the v$process table ( join paddr in v$session to addr in v$process ). The OS process id is the spid in v$process. Kill this from the OS end.

You might also want to move to SQL*Net v2.x and use the sqlnet.expire_time parameter to get rid of zombie processes.

Hope this helps

Hope this helps

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia

Disclaimer : All opinions are truly and just mine.

ctripath_at_ucs.att.com (ctripath_at_ucs.att.com) wrote:


>Hi,

>   Is there any simple way of releasing the row locks assosiated with a table 


>without shutting down and then restarting the database. Is there any DBA

>utility provided by oracle or SQL statements that can achieve it with the

>database online.

>

>We have encountered this problem as some jobs running to update the

>database have to abnormally be terminated, and they hold the locks, which

>prevents further action on these tables.

>

>Thanks

>Chinmoy

>

>

>

>