ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified Cause: Resource interested is busy. Action: Retry if necessary.

ORA-00054 error is the most commonly faced error by the oracle users. This error is related to table locking or object locking. ORA-00054 occur whenever concurrent DDL operations or DML Update/Delete/Insert/Select for Update operations are performed on the same table /object without proper commits.

Usually when ORA 00054 error occurs from a session, at that instant some other session might have acquired "exclusive" lock with uncommited changes on the table or object common to both the sessions.


ORA-00054 Resolution/Fix :

1. Identify the locking session - Find which session or user is blocking your operation.
2. If the lock is free ie. no locking session identified in the first step then you can simply resubmit your operation/statement.
3. If there is a lock then analyze the locking session and user. Based on the priority of your operation vs. locking session/user priority decide whether to wait until lock has been removed or terminate/kill the locking session and proceed with your operation. Usually we end up in waiting for the other session to complete.

So now the question is how to identify the locking sessions?

There are number of inernal tables and views Oracle provides for identifying locks,lock status and their session information.

DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on
DBA_DDL_LOCKS – Shows all DDL locks held or being requested
DBA_DML_LOCKS - Shows all DML locks held or being requested
DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
DBA_LOCKS - Shows all locks or latches held or being requested
DBA_WAITERS - Shows all sessions waiting on, but not holding waited for locks

These tables or views may not contain all the information about locked session, usename ,process name etc. So you may have to join with v$session , v$locked_object and v$process views to obtain detailed information about the locks.


ORA-00054 and Locking Session Identification Demo:

For this demo you may need use two or three sessions. In the first session perform a DML operation (INSERT) and do not commit
Then in the second session perform another DML operation and try to acquire the lock (ie. select for update ) , we will encounter ORA-00054 error.
To find the locking session execute the provided @lockinfo.sql script
To find the locking sesssion login info execute @Locklogininfo.sql script.

Example:

1st Session:

SQL> create table mytab (n number);

Table created.

SQL> grant all on mytab to migr1dba;

Grant succeeded.

SQL> insert into mytab values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> update mytab set n=2 where n = 1;

1 row updated.

SQL>
........
No Commit yet
........

2nd Session :

SQL> declare
2 n number;
3 begin
4 select n into n from migr1.mytab for update nowait;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 4


SQL>

3rd Session:

SQL> @lockinfo.sql

Wed Oct 15 page 1
Lock Information

Machine name Unix RBS
Locked Object Oracle Username O/S Username of Locker Process SID/Ser# Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
MIGR1.MYTAB MIGR1 (INACTIVE) mig05 INFTEST 9281 151,222 _SYS 2008/10/15 05:39:39
SMU4
$


SQL>

SQL> @Locklogininfo.sql

Oracle Username O/S Username Computer/Terminal SID/Ser# STATUS ID Logon Date/Time Program
-------------------- -------------- --------------------- ------------ -------- ------------ -------------------- --------------------------------------------------
oracle INFTEST 157,1 ACTIVE 4773 2008/10/13 16:47:41 oracle@INFTEST (MMNL)
oracle INFTEST 160,7 ACTIVE 4771 2008/10/13 16:47:41 oracle@INFTEST (MMON)
MIGR1 mig05 INFTEST 151,222 INACTIVE 9281 2008/10/15 05:39:39 sqlplus@INFTEST (TNS V1-V3)
MIGR1DBA mig05 INFTEST 155,34448 INACTIVE 9613 2008/10/15 05:45:58 sqlplus@INFTEST (TNS V1-V3)
MIGR1 mig05 INFTEST 153,65380 ACTIVE 9969 2008/10/15 05:56:49 sqlplus@INFTEST (TNS V1-V3)

14 rows selected.

SQL>


Lock Info Identification Scripts:

The following scripts helps in finding the locking sessions, users , process and their status information.

lockinfo.sql script:

ttitle "Lock Information"
set linesize 150
set echo off
col oruser format a16 heading "Oracle Username"
col osuser format a13 heading "O/S Username"
col obj format a20 heading "Locked Object"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a19
col rs heading "RBS|Name" format a4
col unix heading "Unix|Process" format a9
col computer heading "Machine name|of Locker" format a20
set linesize 120
select owner||'.'||object_name obj
,oracle_username||' ('||s.status||')' oruser
,os_user_name osuser
,machine computer
,l.process unix
,s.sid||','||s.serial# ss
,r.name rs
,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from v$locked_object l
,dba_objects o
,v$session s
,v$transaction t
,v$rollname r
where l.object_id = o.object_id
and s.sid=l.session_id
and s.taddr=t.addr
and t.xidusn=r.usn
order by osuser, ss, obj
/

Locklogininfo.sql script:

set linesize 200
set echo off
set feedback on
col oruser format a20 heading "Oracle Username"
col osuser format a14 heading "O/S Username"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a20
col computer heading "Computer/Terminal" format a21
col program format a50 heading "Program"
col process format 999999 heading "OS|Process|ID"
select username oruser
,osuser osuser
,decode(substr(machine,1,7),'TENFOLD',
substr(machine,9),machine) computer
,s.sid||','||s.serial# ss
,status
,process
,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
,program
from v$session s
order by time
/
ttitle off

Conclusion:


ORA-00054 error is the most common error caused by the table or object locks due to concurrent DDL and DML(insert/delete/update/select for update) operations from different sessions.
The resolution for this error is finding the locking sesssion & user then based on priority either wait or terminate the locking session and resubmit the job/operation for successful completion.

In this article scripts have been provided for identifying the locking sessions so that readers can get benifited with the readily available scripts for lock identifiction,

More Details at : http://ora-00054.ora-code.info/

I hope you enjoyed this article

Mendus, Oracle OCP
http://www.ora-code.info/