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.
Rebuilding the index online is not a cure, either. It also requires the lock to be released. Otherwise, you'll see no return, no error and the indefinite waiting.
Solution
You need to told the owner to release the lock, a COMMIT or ROLLBACK is necessary to explicitly release the lock, or you can kill sessions of the lock owner.