- 10 Nov, 2021
- 0 Comments
- 1 Min Read
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-00054: resource busy and acquire with NOWAIT specified
Cause: The NOWAIT keyword forced a return to the command prompt because a resource was unavailable for a LOCK TABLE or SELECT FOR UPDATE command.
Action: Try the command after a few minutes or enter the command without the NOWAIT keyword.
Example:
1 2 3 4 | SQL> alter table emp add (mobile varchar2(15));*ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified |
How to avoid the ORA-00054:
- Execute DDL at off-peak hours, when database is idle.
- Execute DDL in maintenance window.
- Find and Kill the session that is preventing the exclusive lock.
Other Solutions:
Solution 1:
In Oracle 11g you can set ddl_lock_timeout i.e. allow DDL to wait for the object to become available, simply specify how long you would like it to wait:
1 2 | SQL> alter session set ddl_lock_timeout = 600;Session altered. |
1 2 | SQL> alter table emp add (mobile varchar2(15));Table altered. |
Solution 2:
Also In 11g, you can mark your table as read-only to prevent DML:
1 2 | <pre>SQL> alter table emp read only;Session altered.</pre> |
1 2 | SQL> alter table emp add (mobile varchar2(15));Table altered. |
Solution 3 (for 10g):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE MYSQL VARCHAR2(250) := 'alter table emp add (mobile varchar2(15))'; IN_USE_EXCEPTION EXCEPTION; PRAGMA EXCEPTION_INIT(IN_USE_EXCEPTION, -54);BEGIN WHILE TRUE LOOP BEGIN EXECUTE IMMEDIATE MYSQL; EXIT; EXCEPTION WHEN IN_USE_EXCEPTION THEN NULL; END; DBMS_LOCK.SLEEP(1); END LOOP;END; |
Solution 4:
1 | Step 1: Identify the session which is locking the object |
1 2 3 4 5 | select a.sid, a.serial#from v$session a, v$locked_object b, dba_objects c where b.object_id = c.object_id and a.sid = b.session_idand OBJECT_NAME='EMP'; |
Step 2: kill that session using
1 | alter system kill session 'sid,serial#'; |