Hi
When you are compiling an object, Oracle has to pin the dependent
objects in an exclusive mode. In rare cases, you may go in to deadlock
situations if your dependent objects has some form of recursive
relationship and need to be compiled too . I would suggest to find the
dependent objects (look at dba_dependencies) and compile them first. Then
try to compile your object. If still you have a problem, then when the
process is hanging, look at the v$session_Wait to find out what the session
is waiting for. If it is waiting for library cache pin on a particular
object then you could look at x$kgllk table to find out who else if using
that particular object(kglnaobj is the object name and you could use this
column ).
Otherwise, before compiling the object from sqlplus , dump the errorstack
like this and then compile the object. Look at the trace file generated to
find the offending session.:
alter session set events '4020 trace name errorstack, level 15';
Hope this helps!!
~
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com
"These are my opinions and does not bind my employer"
"Mohammad
Rafiq" To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
<rafiq9857@ho cc:
tmail.com> Subject: RE: ORA-04020: deadlock detected
while trying to lock
Sent by: object...
root@fatcity.
com
02/09/01
08:36 AM
Please
respond to
ORACLE-L
It also memory related issue. Reboot your(tired) box (if possible) and try
again. You will see objects will be compiled without locking problem
and will be much fast....
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Fri, 09 Feb 2001 05:10:25 -0800
I saw this note on MetaLink that may help.
Rick
Error: ORA 4020
Text: deadlock detected while trying to lock object %s%s%s%s%s
----------------------------------------------------------------------------
---
Cause: While trying to lock a library object, a deadlock is detected.
Explanation:
During compilation we hit a deadlock situation.
This is generally caused by a circular dependency of some form.
Diagnosis:
Find out what objects are being compiled in what order.
Changing the order of creation should prevent this.
When creating objects a SAFE order of creation is:
TABLE, PACKAGE, PACKAGE BODY, TRIGGER
> -----Original Message-----
> From: Uldis [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, February 09, 2001 7:05 AM
> To: Multiple recipients of list ORACLE-L
> Subject: ORA-04020: deadlock detected while trying to lock
object...
>
> Need guru! :)
>
> At attempt to compile procedure we're getting subj. Nevertheless there
is
> no
> more sessions accessing the schema.
>
> About environment:
> Oracle8i Enterprise Edition Release 8.1.6.0.0 - 64bit Production
> PL/SQL Release 8.1.6.0.0 - Production
> CORE 8.1.6.0.0 Production
> TNS for Solaris: Version 8.1.6.0.0 - Production
> NLSRTL Version 3.4.0.0.0 - Production
>
> TIA,
> Uldis
>
> Uldis Pavuls
> DBA, Tieto Konts Financial Systems Ltd.
> Kr.Barona 32, Riga, Latvia, LV 1011
> phone +371 7 286 660, fax +371 7 243 000
> mailto:[EMAIL PROTECTED], http://www.konts.lv
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Uldis
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
