Monday, February 2, 2015

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

While altering or dropping CONSTRAINTS, COLUMNS or INDEXES, you may face this error 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.

12c Enhancement
In 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


No comments: