Oracle Scratchpad

June 27, 2011

DDL

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)
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;
/

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.

 

4 Comments »

  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.

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

    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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Blog at WordPress.com.