I work with Oracle more than I can remember, but every day I find something new.
This time I’m asked to drop one index on the production system, which is the simplest task you can get, right?
I new there had to be a problem with that, otherwise someone else would do it.
In any case, the first attempt to drop the index (after verifying that it was safe to drop it) resulted in:
DROP INDEX <index_name>;
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
After the first attempt, I’ve decided to use new 11g feature – DDL_LOCK_TIMEOUT:
ALTER SESSION SET ddl_lock_timeout=30;
DROP INDEX <index_name>;
By executing this command, my session waits up to 30 seconds trying to acquire a lock.
Unfortunately, even 30 seconds was not enough due to some other issues that are out of scope of this article.
Lastly, I tried to change the state of the index to UNUSABLE.
On several occasions, I wondered why this option existed after all, because the only operation you can do after putting the index in unusable state is either a DROP or REBUILD index.
Here is the answer.
When I executed the following command:
ALTER INDEX <index_name> UNUSABLE;
I was able to finally drop heavily used, but problematic index.
DROP INDEX <index_name>;
In 11g you have another option – making an index INVISIBLE.
While UNUSABLE index is ignored by CBO (Cost Based optimizer) and is not maintained by DML, INVISIBLE index is just ignored by CBO, which means in this case INVISIBLE option is not a way to go.
In 12c and on there are further DDL online options staying on your disposal, but as described case happened on 11g version of Oracle database, I couldn’t use those options.
Summary:
As you can see, to perform even the most trivial tasks, you has to be familiar with the system in which you work and be prepared for surprises (plan B), especially in heavily used production environments.
Under normal circumstances, ddl_lock_timeout, a new 11g feature of Oracle rdbms, would solve the problem.
In this case, ddl_lock_timeout did not help since more than 200 sessions locked the index I wanted to delete, so the last option to put the index in unusable state had to be used.
Comments