ORA-00054: resource busy and acquire with NOWAIT specified

A common error we face on day to day life. Happens when someone tries to modify a table which is already locked by other user.

Now there are couple of ways to get rid of it.

   1. Check to see which user is locking the object and then kill the session.

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';

alter system kill session 'sid,serial#'; 

2. In 11g we can set ddl_lock_timeout to allow DDL to wait for the object becomes available. We can specify how long we’d want to wait for executing DDL.

SQL> alter session set ddl_lock_timeout = 600;

Session altered.

SQL> alter table emp add (gender varchar2(10));

Table altered.

  3. If you don’t want to hamper other users, then either try executing DDL at off-peak hours or

  4. Wait for few minutes until the other user releases the lock on same object.

  5. In 11g you can put a table in readonly mode to ensure no one locking it and then execute your command.

SQL> alter table emp read only;

Session altered.

 SQL> alter table emp add (gender varchar2(10));

Table altered.