Script to overcome ORA-54 during DDL

From: Jeremiah Wilton <jwilton_at_speakeasy.net>
Date: Fri, 25 Jun 2004 11:55:51 -0700 (PDT)
Message-ID: <Pine.LNX.4.58.0406251148240.18361@shell1.speakeasy.net>

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
http://www.speakeasy.net/~jwilton

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org 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

---

Received on Fri Jun 25 2004 - 13:52:36 CDT