From oracle-l-bounce@freelists.org Sat Jan 1 06:05:01 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j01C50i04947 for ; Sat, 1 Jan 2005 06:05:00 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id j01C4vn04942 for ; Sat, 1 Jan 2005 06:04:57 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 871BB72C55A; Sat, 1 Jan 2005 07:11:29 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 12622-76; Sat, 1 Jan 2005 07:11:29 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D27D472C585; Sat, 1 Jan 2005 07:11:27 -0500 (EST) Message-ID: <015701c4effa$c6c41c20$6702a8c0@Primary> From: "Jonathan Lewis" To: References: <6.2.0.14.0.20041231221516.04dd6b28@pop.singnet.com.sg> Subject: Re: How to avoid or handle the ORA-0054s Date: Sat, 1 Jan 2005 12:09:42 -0000 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-archive-position: 14288 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: jonathan@jlcomp.demon.co.uk X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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" To: 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