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.
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 UTC Jun 27,2011 |
Scott,
Thanks for that – setting a time limit rather than a count is another good idea (arguably a better idea, actually).
Comment by Jonathan Lewis — June 28, 2011 @ 6:06 am UTC Jun 28,2011 |
nice one pretty helpful
Comment by Vinay G. Liyar — July 13, 2011 @ 9:59 am UTC Jul 13,2011 |