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

  • From: J.Velikanovs@xxxxxxxx
  • To: hkchital@xxxxxxxxxxxxxx
  • Date: Sun, 2 Jan 2005 15:51:48 +0200

Another simple method to achieve your purpose:

DROPME:gonza> lock table testddl in exclusive mode;
-- Waiting until all concurrent DML has been done.

Table(s) Locked.

DROPME:gonza> alter index testddl_i1 rebuild;

Index altered.

PS Replace "alter ... rebuild;" with "drop index ... ; lock table ...; 
create index ..." if you want.

+371 9268222 (+2 GMT)
Thank you for teaching me.

+371 9268222 (+2 GMT)
Thank you for teaching me.

Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
01.01.2005 05:05
Please respond to hkchital
        To:     oracle-l@xxxxxxxxxxxxx
        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 
BMJI issues in and we haven't yet gone to for this 
particular database.

Hemant K Chitale



Other related posts: