Re: Script to overcome ORA-54 during DDL
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Jun 2004 23:20:20 +0100
I thought it was pretty good for five minutes -
and I did leave in a CPU-saving sleep.
The timeout problem is left as an exercise
for the reader.
Held cursors shouldn't be a problem - that's
why library cache locks are called breakable
parse locks. ("should" and "won't" are, of
course, two different words).
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: "Stephane Faroult" <sfaroult@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, June 25, 2004 10:03 PM
Subject: Re: Script to overcome ORA-54 during DDL
I am not sure that in some cases this couldn't loop for much longer than
expected, and I'd definitely put a limit on the number of iterations.
The problem is chiefly what is executing the DML, and how. Remember that
Pro*C keeps cursors open even when they are closed in the program -
unless you explicitly ask for them to be released. That was the case at
least the last time I used Pro*C, which is admittedly a long time ago. I
distinctly remember a problem I once had of trying to drop a table after
having queried it, in spite of the SELECT cursor being quite properly
closed. For what I remember of Forms and max_open_cursors being bumped
to astronomical levels, Pro*C isn't alone in this case. No such problem
if the DML is issued from SQL*Plus, which only handles a single cursor,
but the programs accessing the table may be slightly more sophisticated.
SF
Jonathan Lewis wrote:
>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
>-----------------------------------------------------------------
>
>
>
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
