ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired

While altering or dropping CONSTRAINTS, COLUMNS or INDEXES, you may face error ORA-00054 message and the reason of this error message is current ongoing transaction(s) on the table.

Following are the examples

SQL>  alter table test_tab set unused (name);  alter table test set unused (name)              * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL>  create index test_tab_idx on test(id);  create index test_idx on test(id)                           * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> drop index test_tab_idx; drop index test_idx            * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The solution to this is to wait until all ongoing transactions in underlying table (TEST_TAB in above example) complete and there is no current transaction in progress.

Please note that CREATE INDEX command with ONLINE option won’t fail with this error message and would wait until all transactions finish and then statement proceed for execution.

Enhancement starting 12c

Starting 12c, you can use ONLINE keyword with DROP INDEX, DROP CONSTRAINT, ALTER INDEX UNUSABLE and SET COLUMN UNUSED commands and command would automatically wait for all transactions to finish before proceeding and won’t return ORA-00054 error message. Following is how we do this starting 12c. First I tried to drop an index without using ONLINE clause and it returned me error ORA-00054 because there was one active transaction on the table/index, in progress by some other session. Then I again tried to drop the index using ONLINE clause, and the command did not return and kept waiting instead of returning ORA-00054 error. After a couple of minutes, the transaction on the table/index completed and index was dropped and prompt returned.

16:22:00 SQL> drop index testidx ; drop index testidx            * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 16:22:06 SQL> drop index testidx online; Index dropped. 16:26:47 SQL>