Re: How to free a locked object?

On Jan 23, 6:47 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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

Hi David, thanks for the reply. It was lot elaborated hence much helpful for many. I've resolved the issue now. gtcol Received on Thu Jan 24 2008 - 01:10:34 CST