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

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: jonathan@xxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Sun, 02 Jan 2005 14:43:21 +0800

Thank you.  I will try using the exception-in-a-loop method.

The ALTER INDEX .. UNUSABLE also returns an ORA-0054

-- of course, I could use the .. UNUSABLE and REBUILD in the 
exception-in-a-loop method !

Thanks !


Hemant
At 08:09 PM Saturday, you wrote:

>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


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


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

Other related posts: