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.