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 //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------