I need to change a view and an index on an active production system. I’m concerned that the change will fail with a “ORA-00054: resource busy” error because I’m changing things that are in use. I engaged in a twitter conversation with @FranckPachot and @DBoriented and they gave me the idea of using DDL_LOCK_TIMEOUT with a short timeout to sneak in my changes on our production system. Really, I’m more worried about backing out the changes since I plan to make the change at night when things are quiet. If the changes cause a problem it will be during the middle of the next day. Then I’ll need to sneak in and make the index invisible or drop it and put the original view text back.
I tested setting DDL_LOCK_TIMEOUT to one second at the session level. This is the most conservative setting:
alter session set DDL_LOCK_TIMEOUT=1;
I created a test table with a bunch of rows in it and ran a long updating transaction against it like this:
update /*+ index(test testi) */ test set blocks=blocks+1;
Then I tried to alter the index invisible with the lock timeout:
alter index testi invisible * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Same error as before. The update of the entire table took a lot longer than 1 second.
Next I tried the same thing with a shorter running update:
update /*+ index(test testi) */ test set blocks=blocks+1 where owner='SYS' and table_name='DUAL'; commit; update /*+ index(test testi) */ test set blocks=blocks+1 where owner='SYS' and table_name='DUAL'; commit; ... lots more of these so script will run for a while...
With the default setting of DDL_LOCK_TIMEOUT=0 my alter index invisible statement usually exited with an ORA-00054 error. But, eventually, I could get it to work. But, with DDL_LOCK_TIMEOUT=1 in my testing my alter almost always worked. I guess in some cases my transaction exceeded the 1 second but usually it did not.
Here is the alter with the timeout:
alter session set DDL_LOCK_TIMEOUT=1; alter index testi invisible;
Once I made the index invisible the update started taking 4 seconds to run. So, to make the index visible again I had to bump the timeout up to 5 seconds:
alter session set DDL_LOCK_TIMEOUT=5; alter index testi visible;
So, if I have to back out these changes at a peak time setting DDL_LOCK_TIMEOUT to a small value should enable me to make the needed changes.
Here is a zip of my scripts if you want to recreate these tests: zip
You need Oracle 11g or later to use DDL_LOCK_TIMEOUT.
These tests were all run on Oracle 11.2.0.3.
Also, I verified that I studied DDL_LOCK_TIMEOUT for my 11g OCP test. I knew it sounded familiar but I have not been using this feature. Either I just forgot or I did not realize how helpful it could be for production changes.
– Bobby
