Re: How to avoid or handle the ORA-0054s

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 1 Jan 2005 12:09:42 -0000

Something like this should work:

    ddl_timeout exception;
    pragma exception_init(ddl_timeout,-54);
 for i in 1..100 loop
   execute immediate 'drop index i1';
   when ddl_timeout then
 end loop;

Can you not do
    alter index xxxx unusable;
    alter index rebuild:

This should scan the table to rebuild the index
and not re-introduce the corruption.


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated Dec 23rd 2004

----- Original Message ----- 
From: "Hemant K Chitale" <hkchital@xxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Saturday, January 01, 2005 3:05 AM
Subject: How to avoid or handle the ORA-0054s

I have a need to regularly Recreate certain Indexes .  {see note below on
WHY !}
This is scripted.  However, the script sometimes errors on the DROP with
and, of course, the CREATE doesn't go through.
We are trying to put a loop to check the spooled output of the script and 
it if the DROP fails.

However, I was wondering if anyone has implemented a technique to handle
and automated the retry of the DDL.

Why I can't use a REBUILD is because it is a corrupt index.
{and surely, the REBUILD does use a WAIT when it switches the indexes.
Why doesn't Oracle allow us to write a DROP ... WAIT ?}

NOTE : Why the Recreate Indexes :
These are 6 BitMap Join Indexes.  A bug in causes occasional 
when querying the table. The solution is to Recreate the Indexes.  I had
this list on 03-Dec on ORA-600 [12700] errors with these BMJIs.

Although is indicated to have a fix, I see some references to other
BMJI issues in and we haven't yet gone to for this
particular database.

Hemant K Chitale



Other related posts: