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

Something like this should work:

declare

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

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.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

• Original Message ----- From: "Hemant K Chitale" <hkchital_at_singnet.com.sg> To: <oracle-l_at_freelists.org> 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 ORA-0054
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 rerun
it if the DROP fails.

However, I was wondering if anyone has implemented a technique to handle ORA-0054s
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 9.2.0.4 causes occasional ORA-600s
when querying the table. The solution is to Recreate the Indexes. I had emailed
this list on 03-Dec on ORA-600 [12700] errors with these BMJIs.

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

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Sat Jan 01 2005 - 06:05:01 CST