Re: ORA-00054 (alter table add partition)

From: Tim Gorman <tim@xxxxxxxxx>
To: peters@xxxxxxxxx
Date: Mon, 19 Oct 2009 16:25:11 -0600

Peter,

I haven't seen this entire thread, so my apologies if I'm repeating what someone else has said already....

ORA-00054 errors are not "random". It is most often a conflict between a DDL command (i.e. ALTER, DROP, TRUNCATE) and a DML command (i.e. INSERT, UPDATE, DELETE). Query the V$LOCK view for all rows where TYPE = 'TM' and look for rows where BLOCK > 0. The column ID1 will have the OBJECT_ID of the object being contended upon, and this can be translated by querying the DBA_OBJECTS view.

Hope this helps...

Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim@xxxxxxxxx
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt

Sylvester, Peter A. wrote:

If you google “ORA-00054” you will certainly see lots of possibilities.

I found that DDL intensive apps (especially with partition maintenance) seem to be prone to seemingly random ORA-00054 errors.

I eventually put all DDL commands through a retry loop that catches the 54 error and waits a second and reruns it.

Generally it always seems to work on the second try.

I suspect that it is due to some kind of internal data dictionary locking.

This is using 10.2 on Linux x64.

--Peter

-- //www.freelists.org/webpage/oracle-l

References:
- RE: ORA-00054 (alter table add partition)
  - From: Sylvester, Peter A.

Re: ORA-00054 (alter table add partition)

Other related posts: