ORA-00054

ORA-00054 resource busy and acquire with NOWAIT specified

1. Simulation
2. Identify the session that has locked the resource
3. Solution


1. Simulation

To simulate this error for learning purposes, do as below in 2 sessions

Session 1 :

[oracle@rac2 ~]$ sqlplus sh/sh;
SQL> create table lock_table (a number);

Table created.

SQL> insert into lock_table values(100);

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> update lock_table set a=500;

1 row updated.

SQL>

---- Do not commit -----

SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------
46 <----

SQL>

Session 2:

[oracle@rac2 ~]$ sqlplus sh/sh;

SQL> declare
b number;
begin
select a into b from lock_table for update nowait;
end;
/  2    3    4    5    6
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at line 4

SQL>


2. Identify the session that has locked the resource

The below query helps to identify the session that has locked the resource that your session is trying to lock.

[oracle@rac2 ~]$ sqlplus / as sysdba

SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id
FROM dba_objects
where object_name='LOCK_TABLE' and object_type='TABLE' AND OWNER='SH');

select s.sid, s.serial#,s.inst_id, s.program,p.spid from gv$session s , gv$process p
where  p.addr =s.paddr
and   s.sid in (46);

-- OR --

set linesize 140
set pages 100
col username       format a20
col "SID,SESSION#" format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ' (' || s.osuser || ')' username
  ,  s.sid || ',' || s.serial# "SID,SESSION#"
  ,  owner || '.' || object_name object
  ,  object_type
  ,  decode( l.block
     ,       0, 'Not Blocking'
     ,       1, 'Blocking'
     ,       2, 'Global') status
  ,  decode(v.locked_mode
    ,       0, 'None'
    ,       1, 'Null'
    ,       2, 'Row-S (SS)'
    ,       3, 'Row-X (SX)'
    ,       4, 'Share'
    ,       5, 'S/Row-X (SSX)'
    ,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
 from       v$locked_object v
 ,  dba_objects d
 ,  v$lock l
 ,  v$session s
 where      v.object_id = d.object_id
 and        v.object_id = l.id1
 and        v.session_id = s.sid
 order by oracle_username,session_id;
 
To identify the locked rows, use the below query:

set lines 200
col object_name for a30
select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
                ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) ROW_ID
from    v$session s
,   dba_objects do
where   s.ROW_WAIT_OBJ# = do.OBJECT_ID
and do.object_name=upper('&OBJECT_NAME_WITHOUT_SCHEMA_NAME')
;

-- OR --

SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$LOCKED_OBJECT L,
    DBA_OBJECTS O,
    V$SESSION S,
    V$PROCESS P,
    V$SQL SQ
WHERE
    L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    AND S.PADDR = P.ADDR
    AND S.SQL_ADDRESS = SQ.ADDRESS;

Output

SQL> SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id
FROM dba_objects
where object_name='LOCK_TABLE' and object_type='TABLE' AND OWNER='SH');  2    3    4

 OBJECT_ID SESSION_ID    INST_ID
---------- ---------- ----------
     82844         46          1  <--- Please note session ID 46

SQL> select s.sid, s.serial#,s.inst_id, s.program,p.spid from gv$session s , gv$process p
where  p.addr =s.paddr
and   s.sid in (46);  2    3

       SID    SERIAL#    INST_ID PROGRAM                                          SPID    
---------- ---------- ---------- ------------------------------------------------ --------
        46         11          1 sqlplus@rac2.rajasekhar.com (TNS V1-V3)          5768

SQL>


3. Solution

You can try executing the query/job after some time (to allow the other session to complete). 

--- OR ----

kill the session that has locked the resource

Syntax: alter system kill session 'SID,SERIAL#,@INST_ID'; (For RAC)
alter system kill session 'SID,SERIAL#';(For Single instance)

SQL> alter system kill session '46,11';

System altered.

SQL>


SQL> declare
b number;
begin
select a into b from lock_table for update nowait;
end;
/  2    3    4    5    6

PL/SQL procedure successfully completed. <-- executed without error after killing problematic session 

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala