Oracle Scratchpad

June 27, 2011


Filed under: Oracle — Jonathan Lewis @ 5:39 pm BST Jun 27,2011

A couple of days ago one of the replies to a question on the Oracle-L listserver suggested using “Jonathan Lewis’ do_ddl function”. This seemed a little odd to me, as I had no idea what my do_ddl function was. After a little search through the archives, though, I discovered that this was a quick and dirty piece of pl/sql I had invented 7 years ago as a possible method of avoiding error  “ORA-00054”.

The suggestion was in response to a posting from Jeremiah Wilton:

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?

This is what I came up with:

create or replace procedure do_ddl(m_sql varchar2)
        in_use exception ;
        pragma exception_init(in_use, -54);
        while true loop
                        execute immediate m_sql;
                        when in_use then null;
                        when others then raise;
        end loop;

Seven years later I think it’s probably worth pointing out that you might want to count the number of times you fail and then give up rather than retrying indefinitely – but apart from that, you might find this a useful little hack for rare occasions (but probably shouldn’t use it for regular production activity).

If you follow the original question, by the way, you will see that Mark Bobak has suggested that do_ddl is redundant in 11g because you can set the  ddl_lock_timeout parameter to address the problem.



  1. I took a very similar approach on one of my busiest applications. Although I didn’t use a procedure to perform the DDL, and left the checks to the modifcation itself. Maybe I’m being irrational, but I’ve never been comfortable with stored procedures having permissions to execute DDL. (and yes, I know that controlling access to the proc should be just as secure)

    I didn’t count the number of attempts, but set a time limit to how long it could spin. The change is actually called by a shell script that performs all deployments, hence the dbms_output which is captured in deployment log files.

      mod_complete  BOOLEAN;
      table_lock    EXCEPTION;
      PRAGMA EXCEPTION_INIT(table_lock, -00054);
      mod_start     DATE;
      mod_complete := FALSE;
      WHILE (NOT mod_complete) AND (SYSDATE < mod_start + 5/24/60)
          EXECUTE IMMEDIATE ('ddl goes here');
      		mod_complete := TRUE;
          DBMS_OUTPUT.PUT_LINE('Table blah has been modified');
        WHEN table_lock THEN
      END LOOP;
      IF mod_complete = FALSE THEN
        DBMS_OUTPUT.PUT_LINE('ERROR- Could not achieve table lock on blah');
      END IF;

    Comment by Scott Lynch — June 27, 2011 @ 9:14 pm BST Jun 27,2011 | Reply

  2. nice one pretty helpful

    Comment by Vinay G. Liyar — July 13, 2011 @ 9:59 am BST Jul 13,2011 | Reply

  3. […] you have to use exceptions and manually wait for a certain amount of time, using Jonathan Lewis do_ddl will do the trick. I cant find my modified script right now, but I’ll try mod it […]

    Pingback by Analyze all Indexes in the DB | The Oracle DB inn — September 3, 2014 @ 2:48 pm BST Sep 3,2014 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by