Re: How to free a locked object?

From: <fitzjarrell_at_cox.net>
Date: Wed, 23 Jan 2008 05:47:26 -0800 (PST)
Message-ID: <91f1de0f-9625-439f-9ff2-ce9e32259d36@e25g2000prg.googlegroups.com>

On Jan 23, 2:37 am, dbagtcol <cx4gt..._at_gmail.com> wrote:

> hi all,
> I'm trying a compile a proc but the run would just go forever and
> throw 'ORA-04021: timeout occurred while waiting to lock object xxx'
> at the end. Upon checking the v$session_wait, i found "latch: cache
> buffers chains" as Event and "Concurrency" as Wait_class. On checking v
> $locked_object, i found one object being held. I don't have access to
> alert_log file. How do I find who is using this object and how do I
> free it for myself?
>
> Appreciate your kind responses.
> gtcol

The alert log won't tell you who (which user and session) is holding that lock, but V$LOCKED_OBJECT will; I'm surprised you didn't see that information from your query. You could run this query (presuming you have DBA access):

select s.username, s.sid, s.serial#, s.osuser, k.ctime, o.object_name object, k.kaddr,
decode(l.locked_mode, 1, 'No Lock',

		      2, 'Row Share',
		      3, 'Row Exclusive',
		      4, 'Shared Table',
		      5, 'Shared Row Exclusive',
		      6, 'Exclusive') locked_mode,
decode(k.type, 'BL','Buffer Cache Management (PCM lock)',


               'CF','Controlfile Transaction',

               'CI','Cross Instance Call',

               'CU','Bind Enqueue',

               'DF','Data File',

               'DL','Direct Loader',

               'DM','Database Mount',

               'DR','Distributed Recovery',

               'DX','Distributed Transaction',

               'FS','File Set',

               'IN','Instance Number',

               'IR','Instance Recovery',

               'IS','Instance State',

               'IV','Library Cache Invalidation',

               'JQ','Job Queue',

               'KK','Redo Log Kick',

               'LA','Library Cache Lock',

               'LB','Library Cache Lock',

               'LC','Library Cache Lock',

               'LD','Library Cache Lock',

               'LE','Library Cache Lock',

               'LF','Library Cache Lock',

               'LG','Library Cache Lock',

               'LH','Library Cache Lock',

               'LI','Library Cache Lock',

               'LJ','Library Cache Lock',

               'LK','Library Cache Lock',

               'LL','Library Cache Lock',

               'LM','Library Cache Lock',

               'LN','Library Cache Lock',

               'LO','Library Cache Lock',

               'LP','Library Cache Lock',

               'MM','Mount Definition',

               'MR','Media Recovery',

               'NA','Library Cache Pin',

               'NB','Library Cache Pin',

               'NC','Library Cache Pin',

               'ND','Library Cache Pin',

               'NE','Library Cache Pin',

               'NF','Library Cache Pin',

               'NG','Library Cache Pin',

               'NH','Library Cache Pin',

               'NI','Library Cache Pin',

               'NJ','Library Cache Pin',

               'NK','Library Cache Pin',

               'NL','Library Cache Pin',

               'NM','Library Cache Pin',

               'NN','Library Cache Pin',

               'NO','Library Cache Pin',

               'NP','Library Cache Pin',

               'NQ','Library Cache Pin',

               'NR','Library Cache Pin',

               'NS','Library Cache Pin',

               'NT','Library Cache Pin',

               'NU','Library Cache Pin',

               'NV','Library Cache Pin',

               'NW','Library Cache Pin',

               'NX','Library Cache Pin',

               'NY','Library Cache Pin',

               'NZ','Library Cache Pin',

               'PF','Password File',

               'PI','Parallel Slaves',

               'PR','Process Startup',

               'PS','Parallel slave Synchronization',

               'QA','Row Cache Lock',

               'QB','Row Cache Lock',

               'QC','Row Cache Lock',

               'QD','Row Cache Lock',

               'QE','Row Cache Lock',

               'QF','Row Cache Lock',

               'QG','Row Cache Lock',

               'QH','Row Cache Lock',

               'QI','Row Cache Lock',

               'QJ','Row Cache Lock',

               'QK','Row Cache Lock',

               'QL','Row Cache Lock',

               'QM','Row Cache Lock',

               'QN','Row Cache Lock',

               'QO','Row Cache Lock',

               'QP','Row Cache Lock',

               'QQ','Row Cache Lock',

               'QR','Row Cache Lock',

               'QS','Row Cache Lock',

               'QT','Row Cache Lock',

               'QU','Row Cache Lock',

               'QV','Row Cache Lock',

               'QW','Row Cache Lock',

               'QX','Row Cache Lock',

               'QY','Row Cache Lock',

               'QZ','Row Cache Lock',

               'RT','Redo Thread',

               'SC','System Commit number',

               'SM','SMON synchronization',

               'SN','Sequence Number',

               'SQ','Sequence Enqueue',

               'SR','Synchronous Replication',

               'SS','Sort Segment',

               'ST','Space Management Transaction',

               'SV','Sequence Number Value',

               'TA','Transaction Recovery',

               'TM','DML Enqueue',

               'TS','Table Space (or Temporary Segment)',

               'TT','Temporary Table',

               'TX','Transaction',

               'UL','User-defined Locks',

               'UN','User Name',

               'US','Undo segment Serialization',

               'WL','Writing redo Log',

               'XA','Instance Attribute Lock',

               'XI','Instance Registration Lock') type

from v$session s, sys.v_$_lock c, sys.v_$locked_object l, dba_objects o, sys.v_$lock k, v$_lock v
where o.object_id = l.object_id

and l.session_id = s.sid
and k.sid = s.sid
and s.saddr = c.saddr
and k.kaddr = c.kaddr
and k.kaddr = v.kaddr
and v.saddr = s.saddr
and k.lmode = l.locked_mode
and k.lmode = c.lmode
and k.request = c.request

order by object;

This should provide all of the information you would need about the lock and who is holding it. And, again, presuming you have DBA access you could kill the 'offending' session; that's the only way you'll clear that lock if it isn't your current session holding it.

If you haven't DBA access then you need to wait until the offending session issues a commit, a rollback or dies.

David Fitzjarrell Received on Wed Jan 23 2008 - 07:47:26 CST