Re: Error of deadlock detected while trying to lock object (ora-04020)

Thanks Anurag. Certainly I have not tried the first point which I had no knowledge on it. I tried the utlrp but all the invalid were still there. Third, I might go deeper in metalink. Of course, i can wipe out the whole thing, it is time for 9i anyway, but I really like the opportunity to learn why so I can design my codes more carefully. Do you think the DBMS_job will cause this because there is dbms_job run around the time when the error occured ( I need to double check).

Chisoon

Anurag Varma wrote:

>"cschang" <cschang_at_maxinter.net> wrote in message
>news:40625518.2020001_at_maxinter.net...
>
>>This is the first time I have such problem since this database was set
>>up 5 years ago. My system is an 8.1.6 on NT 4 (w sp 6a) with one schema.
>>What happened was this morning when I tried to reload the dump file
>>(resynchronize the database). The dump file was from itself. The message
>>came out when I tried to drop the schema owner as "drop user myowner
>>cascade;" and the sys showed Error: ora-04020: deadlock detected while
>>trying to lock object <atable>". So I went on-line to search the
>>solutions and tried several methods but none of them work. Here was what
>>I tried.
>>1. from metalink to suggest to run the %oracle_home\rdbms\admin\utlrp.sql
>>not working.
>>
>--snip--
>
>>5. So I continue to reload the schema dump file and recompile every
>>invalid object except this one and the related view, procedure, package
>>(obvious), then try to drop the object, still same error
>>
>--snip--
>
>>set up, but no problem. Because the explanation of the occurrence is due
>>to multiple user tried to modify the same row of the table. However,
>>there was no one at the moment. So I wonder anyone has a solution for
>>such problem and to remove the deadlock. The only one I know is to wipe
>>out the database and rebuild a new one.
>>At the time I drop the user, the system also return error: ora-00604:
>>error occurred at recursive SQL level 1.
>>
>>Thank for any suggestion, your help is greatly appreciated.
>>
>>C Chang
>>
>
>You seem to have hit a BUG. Hate to tell you this but it might just be
>easier to
>ask Oracle Support for help.
>
>ora-4020 is a library cache deadlock. The explanation of its occurence is
>not
>as you describe. Its occurence is usually due to an Oracle bug .. and/or
>caused
>by invalid triggers/objects.
>
>You can also try the following:
>* alter session set events '4020 trace name processstate forever, level 10';
> and try the drop table ...
> a trace file will be generated which might give you more clue about the
>deadlock.
> You can post the relevant parts of the trace here.
>* What user did you run utlrp as? Do you still have invalid objects for the
>table ..
> how about invalid objects as the SYS user?
>* Search on metalink for any relevant bugs which might help you solve
>this...
>
>Considering that this is a test database .. maybe eventually it might just
>be easier
>for you to rebuild the database..
>
>Anurag
>
>
Received on Thu Mar 25 2004 - 15:04:22 CST