How to free a locked object?
dbagtcol
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
fitzj...@cox.net
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
dbagtcol
Hi David, thanks for the reply. It was lot elaborated hence much
helpful for many. I've resolved the issue now.
gtcol