Getting around ORA-00054 in Oracle 10g

14 Aug 2012

In 10g, if you are trying to make DDL changes on a busy object, you’re met with:

ORA-00054: resource busy and acquire with NOWAIT specified

If you google how to fix this, you're told to re-run the transaction, or find out what’s locking the object. If you're trying to make schema changes on a live system in a scripted/automated way, you don't want to interfere with production processes and you need to deal with re-running the transaction in an automated way. This is the solution:

  1. DECLARE resource_busy EXCEPTION;
  2. PRAGMA EXCEPTION_INIT(resource_busy, -54);
  3. PROCEDURE try_change IS
  4. BEGIN
  5. dbms_lock.sleep(0.5);
  6. EXECUTE IMMEDIATE 'alter table .....';
  7. EXCEPTION WHEN resource_busy THEN try_change;
  8. END;
  9. BEGIN
  10. try_change;
  11. END;
  12. /


Add new comment

The content of this field is kept private and will not be shown publicly.

Restricted HTML

  • Allowed HTML tags: <a href hreflang target> <em> <strong> <cite> <blockquote cite> <pre> <ul type> <ol start type> <li> <dl> <dt> <dd> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.

Get a quote in 24 hours

Wether a huge commerce system, or a small business website, we will quote the project within 24h of you pressing the following button: Get quote