ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified is a common error seen in Oracle Database

Reference: Oracle documentation

This generally happens when you try to execute a DDL on the table which is locked by transaction .It also happens if  select for update statement is executed with NOWAIT option

Example

SQL> alter table emp add (middlename varchar2(15));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> create index  emp_idx on emp(emp_no);

*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> Select * from emp for update NOWAIT;

*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

How to prevent the ORA-00054 error

1.Do the DDL in Maintenance window or off peak hours when no transaction are going on

2.  With 11g, we have DDL_LOCK_TIMEOUT,

This simply specify how long you would like it to wait for the DDL lock

SQL> alter session set ddl_lock_timeout = 600;
Session altered.

SQL> alter table emp add (middlename varchar2(15));

Table Altered

3.  We can kill the transaction which hold the oracle locks and then proceed with it

column sid_ser format a12 heading 'session,|serial#'; 
column username format a12 heading 'os user/|db user'; 
column process format a9 heading 'os|process'; 
column spid format a7 heading 'trace|number'; 
column owner_object format a35 heading 'owner.object'; 
column locked_mode format a13 heading 'locked|mode'; 
column status format a8 heading 'status'; 
select 
    substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser, 
    substr(l.os_user_name||'/'||l.oracle_username,1,12) username, 
    l.process, 
    p.spid, 
    substr(o.owner||'.'||o.object_name,1,35) owner_object, 
    decode(l.locked_mode, 
             1,'No Lock', 
             2,'Row Share', 
             3,'Row Exclusive', 
             4,'Share', 
             5,'Share Row Excl', 
             6,'Exclusive',null) locked_mode, 
    substr(s.status,1,8) status 
from 
    v$locked_object l, 
    all_objects     o, 
    v$session       s, 
    v$process       p 
where 
    l.object_id = o.object_id 
and l.session_id = s.sid 
and s.paddr      = p.addr 
and s.status != 'KILLED'
/

Once you have find the blocking session and decided to kill oracle session ,we can use below query to generate the kill session sql

select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid=&1;

4.If you are getting ORA-00054: resource busy and acquire with NOWAIT specified in Application form ,then proceed as below

We have case where we encounter ORA-00054: resource busy and acquire with NOWAIT specified in Application form.  Now in this case it become very difficult to find the locks  as the application does not wait for the lock. This generally happens when application issues select for update with no wait option.We can find locks through dba_waiters when the session waits for the lock.As it is locking with nowait session , we cannot just find it.

We will need to find the oracle sql trace for the session  and reproduce the issue. Once the trace is available.  We need to look for err=54 in the trace file

PARSING IN CURSOR #18446744071497070208 len=167 dep=1 uid=173 oct=3 lid=173 tim=3315832569154 hv=817497356 ad='31afc8bcd0' sqlid='6gvfwr8sbn18c'
SELECT GROUP_MARK_ID FROM MTL_INV_SERIAL_NUMBERS WHERE CURRENT_ORGANIZATION_ID = :B3 AND INVENTORY_ITEM_ID = :B2 AND SERIAL_NUMBER = :B1 FOR UPDATE OF GROUP_MARK_ID NOWAIT
END OF STMT
PARSE #18446744071497070208:c=53,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1906360410,tim=3315832569152
BINDS #18446744071497070208:
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=ffffffff7c203028 bln=22  avl=03  flg=05
value=23
Bind#1
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=0 off=24
xsbbbfp=ffffffff7c203040 bln=22  avl=05  flg=01
value=11111
Bind#2
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=871 siz=0 off=48
kxsbbbfp=ffffffff7c203058 bln=32  avl=08  flg=01
value="1222333"
EXEC #18446744071497070208:c=1167,e=1167,p=0,cr=9,cu=1,mis=0,r=0,dep=1,og=1,plh=1906360410,tim=3315832570599
ERROR #18446744071497070208:err=54 tim=3315832570735
STAT #18446744071497070208 id=1 cnt=0 pid=0 pos=1 obj=0 op='FOR UPDATE  (cr=0 pr=0 pw=0 time=0 us)'

The line which shows the error and portion above shows the statement which is giving the error

SELECT GROUP_MARK_ID FROM MTL_INV_SERIAL_NUMBERS WHERE CURRENT_ORGANIZATION_ID = :B3 AND INVENTORY_ITEM_ID = :B2 AND SERIAL_NUMBER = :B1 FOR UPDATE OF GROUP_MARK_ID NOWAIT

Now to find the blocking session ,we need to fire the statement in sqlplus with NOWAIT option

SELECT GROUP_MARK_ID FROM MTL_INV_SERIAL_NUMBERS WHERE CURRENT_ORGANIZATION_ID = :B3 AND INVENTORY_ITEM_ID = :B2 AND SERIAL_NUMBER = :B1 FOR UPDATE OF GROUP_MARK_ID ;

Then this session will wait and we can easily find the blocking session from dba_waiters and kill the blocking session.

5.With Oracle 11g and Oracle 12c ,we have lot of DDL activities which can be done online with out interruption of the error ORA-00054

SQL> create index  emp_idx on emp(emp_no) online;

Starting 12c, you can use ONLINE keyword with DROP INDEX, DROP CONSTRAINT, ALTER INDEX UNUSABLE and SET COLUMN UNUSED commands

Related Articles

ORA-00942 table or view does not exist
ORA-28000 the account is locked
ORA-28002
ORA-00904: invalid identifier
ORA-01017: invalid username/password; logon denied
alter system kill session