I 've used something like this in the past, except I also exclusively lock
the table to which I am trying to apply the DDL.
The idea being that I wait to get the exclusive lock, and having got the
exclusive lock the DDL can acquire the lock in nowait mode. Its not
perfect, but I find I need less attempts to get the DDL to execute.
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Jonathan Lewis
Sent: 25 June 2004 21:37
To: oracle-l@(protected)
Subject: Re: Script to overcome ORA-54 (See ORA-54.ora-code.com) 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@(protected) >
To: <oracle-l@(protected) >
Sent: Friday, June 25, 2004 7:55 PM
Subject: Script to overcome ORA-54 (See ORA-54.ora-code.com) during DDL
Sometimes when trying to perform DDL on really hot objects (heavy
read/DML), I get:
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
