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.

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e0.htm

Example:
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:
 
SQL> alter session set ddl_lock_timeout = 600;
Session altered.

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:
SQL> alter table emp read only;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered.


Solution 3 (for 10g):
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: 

Step 1: Identify the session which is locking the object
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_id
and OBJECT_NAME='EMP';

Step 2: kill that session using
alter system kill session 'sid,serial#'; 
 
Related Posts:
 - ORA-00027 cannot kill current session 
 - Stopping Query without killing the session 

24 comments:

  1. very, very thanks


    Solution 4.... OK!

    ReplyDelete
  2. thanks a lot . i face the same problem and solution 4 is the perfect one

    ReplyDelete
    Replies
    1. nice to know that i was able to solve your porb :)

      Delete
  3. Thanks a lot, I used solution 3

    ReplyDelete
  4. Thanks a lot. I used Solution 4. It worked perfectly for me.

    Regards,

    ReplyDelete
  5. Thanks a lot. I used solution #4 and it worked perfectly.

    Regards,
    Hamid

    ReplyDelete
  6. Thanks, mate. You saved my day.. :)

    ReplyDelete
  7. thanks for solution 4 in particular

    ReplyDelete
  8. Thank you very much. Solution 4 helped to resolve the issue

    ReplyDelete
  9. Optin # 3 & 4 tested. Working good. Thnx a lot.
    sajid Multan Pakistan

    ReplyDelete
  10. very thanks.. Solution 4 OK ..

    ReplyDelete
  11. This solution works fine but what is to be done when this error appears in application screen as follows:

    I have 2 users updating the same record, one from a java screen one from Oracle Forms screen. In Oracle forms Error Ora-20001 - Ora-0054 appears and in java an error that changes willd e discarted as there were updated by another user.

    So how to avoid this message to appear?
    (This question is from a programmer point of view)

    ReplyDelete
    Replies
    1. Your application is making DDL operations? Not a good idea for me. But still you can opt for solution 3 for application level.

      Delete
    2. It's about a DML operation, and as i see in forms it's just commit_form used. Maybe DBMS_LOCK.SLEEP(1) can help? I have to read more about this.

      Delete
  12. This application is making DML op, one simple update. I am a developer on Oracle side...I'm not sure that solution 3 works..but I will try it.

    ReplyDelete
  13. Solution 4 really helped me..
    Thanks alot.

    ReplyDelete
  14. thank you for posting very helpfull

    ReplyDelete
  15. Thank you for posting.. good work.. very good posting

    ReplyDelete
  16. what is the object_name? is it same as the schema name?

    ReplyDelete
    Replies
    1. Object means any table, index, cluster, view, package, procedure, function or others. you can find object type in your schema using following query
      select distinct object_type from user_objects;

      Delete
  17. Tested solution 2 , which works well in 11g.

    Thanks.

    ReplyDelete