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

ORA-00060: deadlock detected while waiting for resource
ORA-00936:missing expression
ORA-02063 remote db error and how to fix it
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-04091: table is mutating trigger
ORA-00604: error occurred at recursive SQL level string
PLS-00222: no function with name exists
ORA-00600: internal error code arguments:
ORA-21000:error number argument to raise_application_error is out of range
ORA-00257: archiver error. Connect internal only, until freed.
ORA-00001: unique constraint
ORA-00017: session requested to set trace event
ORA-00018: maximum number of sessions exceeded
ORA-00019: maximum number of session licenses exceeded
ORA-00020: maximum number of processes exceeded.
ORA-00021: session attached to some other process; cannot switch session
ORA-00022: invalid session ID; access denied
ORA-00023: session references process private memory; cannot detach session
ORA-00024: logins from more than one process not allowed in single-process mode
ORA-00025: failed to allocate string
ORA-00026: missing or invalid session ID
ORA-00027: cannot kill current session
ORA-00028: your session has been killed
ORA-00029: session is not a user session
ORA-00030: User session ID does not exist.
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-00055: maximum number of DML locks exceeded
ORA-00060: deadlock detected while waiting for resource
ORA-00257: archiver error. Connect internal only, until freed.
ORA-00600: internal error code arguments
ORA-00604: error occurred at recursive SQL level string
ORA-00936:missing expression
ORA-00997: illegal use of LONG datatype
ORA-01000:maximum open cursors exceeded
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
ORA-01480: trailing null missing from STR bind value
ORA-01555: snapshot too old: rollback segment number string with name "string" too small
ORA-03237: Initial Extent of specified size cannot be allocated in tablespace
ORA-04030: out of process memory when trying to allocate string bytes
ORA-04031: unable to allocate string bytes of shared memory
ORA-04091: table is mutating trigger
ORA-12154: TNS:could not resolve the connect identifier specified - Oracle Error
ORA-21000:error number argument to raise_application_error is out of range
ORA-28009: connection to sys should be as sysdba or sysoper
PLS-00222: no function with name exists
ORA-01422: exact fetch returns more than requested number of rows
Share on Google Plus

About Mindsforest

    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment