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 
 - ORA-01031: insufficient privileges 

37 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 4. It worked perfectly for me.

    Regards,

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

    Regards,
    Hamid

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

    ReplyDelete
  6. thanks for solution 4 in particular

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

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

    ReplyDelete
  9. very thanks.. Solution 4 OK ..

    ReplyDelete
  10. 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
    3. Very helpful post, I was able to resolve our db connectivity issue through it

      Delete
  11. 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
  12. Solution 4 really helped me..
    Thanks alot.

    ReplyDelete
  13. thank you for posting very helpfull

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

    ReplyDelete
  15. 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
  16. Tested solution 2 , which works well in 11g.

    Thanks.

    ReplyDelete
  17. Gr8, thanks sol 4 worked for me.

    ReplyDelete
  18. Thanks a lot Nimish..I tried solution 4.!!
    works like a charm.

    ReplyDelete
  19. Solution 4 Is Good One.
    Thank u

    ReplyDelete
  20. ORA-00054 is very common issue we generally get, the way you have explained is great specially to find session locking the object. Thanks for sharing once again.

    ReplyDelete
  21. how to resolve resource busy error in Oracle? i don't want to kill any session. i just want to add wait and loop logic. can anyone help me on this?

    ReplyDelete
  22. by combining Sol 4 and 3 and run a loop to check when the lock is released from V$Locked_Object and at that very moment can trigger an asynchronous job to Alter The Table then. If initially the Table is locked we can send out a DBMS_ALERT message that it's locked and we scheduled an offline job to alter The table and the User will get the Update once it's done. But it will not affect the DML Session or the User's session

    ReplyDelete
  23. Hey Thanks dear............

    ReplyDelete
  24. Good housekeeping, properly used network cables.

    ReplyDelete
  25. Blog Commenting is something that helps to get quality backlinks for your website. There are various websites that boost the traffic of your website. It is also a nice platform to connect with your audiences.busy software free download

    ReplyDelete