Skip to content

How to Resolve ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ORA-00054

There're several cases that throw ORA-00054.

Alter Table Move Tablespace

We tried to move table to another tablespace.

SQL> show user
USER is "HR"
SQL> alter table employees move tablespace users;
alter table employees move tablespace users
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Alter Table Read Only

Or tried to make the table read only.

SQL> alter table employees read only;
alter table employees read only
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Drop Table

Even dropping the table is impossible.

SQL> drop table employees;
drop table employees
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Create Index

We can't create index at this moment.

SQL> create index emp_mgr_idx on employees (manager_id);
create index emp_mgr_idx on employees (manager_id)
                            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Alter Index

Next, we tried to rebuild index to another tablespace.

SQL> alter index emp_email_uk rebuild tablespace example;
alter index emp_email_uk rebuild tablespace example
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ORA-00054 means that the table you want to operate with has a lock on it by someone else and it cannot release the lock at this moment. You need to wait for the lock released.

Solution

You need to told the owner to release the lock, a COMMIT or ROLLBACK is necessary to explicitly release the table lock, or you can kill sessions of the lock owner.

Leave a Reply

Your email address will not be published. Required fields are marked *