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

"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 Wed Mar 24 2004 - 22:16:52 CST