Re: Script to overcome ORA-54 during DDL

This looks about right (except for the formatting);

create or replace procedure do_ddl(m_sql varchar2)
as
 in_use exception ;
 pragma exception_init(in_use, -54);
begin
 while true loop
  begin
   execute immediate m_sql;
   exit;
  exception 
   when in_use then null;
   when others then raise;
  end;
  dbms_lock.sleep(0.01);
 end loop;
end;
/

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

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

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


----- Original Message ----- 
From: "Jeremiah Wilton" <jwilton@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, June 25, 2004 7:55 PM
Subject: Script to overcome ORA-54 during DDL


Sometimes when trying to perform DDL on really hot objects (heavy
read/DML), I get:

ORA-00054: resource busy and acquire with NOWAIT specified.

I guess this is because you need to obtain a library cache lock in
order to change the object definition.  Since there is no enqueue
mechanism for this resource, you can't just 'lock table ... in
exclusive mode;'  All that gives you is a DML lock.

One way to avoid this is to write a PL/SQL routine that uses DBMS_SQL
and spins trying to run the DDL, stopping only when it succeeds.  This
seems to work most of the time.

Does anyone have a script for doing the above that they would like to
share?

Please don't tell me to just use dbms_redefinition.  That is
unnecessarily complex when the above technique can be used instead.

Thanks

--
Jeremiah Wilton


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: