Saturday, January 3, 2009

ORA-00054: resource busy and acquire with NOWAIT specified

Problem Description
In my production database Oracle 10.2g while I was adding column to one of my transaction table it fails with ORA-54 error as below.

SQL> alter table student add b number;
alter table student add b number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Description of the Problem
Let's try to produce the problem in our development environment. I have opened two session that connected to database under a schema. In one session, I have created a table and inserted data into it.
SQL> create table a (a number);

Table created.

SQL> insert into a values(1);

1 row created.

I did not yet committed data in session 1. Now in another session whenever I try to any ddl like (alter table, drop table) ORA-00054 will produce.

In another session,
SQL> alter table a add b number;
alter table a add b number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> drop table a;

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

SQL> lock table a in exclusive mode nowait;

lock table a in exclusive mode nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


Cause of the Problem
Whenever you try to do any structural changes on a table oracle try to lock the table exclusively with NOWAIT option(this is in 10.2g while in 11g you can change the wait timeout). If oracle fails to lock the table exclusively then ORA-00054 will occur.

Solution of the Problem
In 10.2g you are limited to several choices to solve the problem. To avoid it,

-Re run the DDL at a later time when the database become idle.
or,

-Kill the sessions that are preventing the exclusive lock.
or,

-Prevent end user to connect to the database and then run the DDL.

You have different views to see locking information about the table.
1)DBA_BLOCKERS: Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting. In our scenario this view will not help.

2)DBA_DDL_LOCKS: It lists all DDL locks held in the database and all outstanding requests for a DDL lock.

3)DBA_DML_LOCKS: It lists all DML locks held in the database and all outstanding requests for a DML lock.
If you query from it in the mode_held field you will see 'row exclusive lock'.
SQL> select mode_held from dba_dml_locks where owner='MAXIMSG';

MODE_HELD
-------------
Row-X (SX)

4)DBA_LOCK: It lists all locks or latches held in the database, and all outstanding requests for a lock or latch.

5)DBA_LOCK_INTERNAL: It displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.

6)DBA_LOCKS is a synonym for DBA_LOCK.

7)DBA_WAITERS: Shows all the sessions that are waiting for a lock.

8)V$LOCK: It lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

9)V$LOCK_ACTIVITY: This view is deprecated.

10)V$LOCKED_OBJECT: This view lists all locks acquired by every transaction on the system.

In order to see locked object query,
SQL> set linesize 130
SQL> set pages 100
SQL> col username       format a20
SQL> col sess_id        format a10
SQL> col object format a25
SQL> col mode_held      format a10
SQL> select     oracle_username || ' (' || s.osuser || ')' username
,  s.sid || ',' || s.serial# sess_id
,  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
/

USERNAME             SESS_ID    OBJECT                    OBJECT_TYPE         STATUS       MODE_HELD
-------------------- ---------- ------------------------- ------------------- ------------ ----------
MAXIMSG (A)          142,232    MAXIMSG.A                 TABLE               Not Blocking Row-X (SX)
OMS (DBA2\ershad)    143,280    OMS.T                     TABLE               Not Blocking Row-X (SX)
OMS (DBA2\ershad)    143,280    OMS.T1                    TABLE               Not Blocking Row-X (SX)

Here we see there is 3 types of locking. In our case the object is A. And we see the sid=142 and serial#=232 is preventing us from locking the table in exclusive mode. So to achieve our task we can kill the session as below.

SQL> alter system kill session '142, 232';

System altered.

SQL> alter table a add b number;

Table altered.

Now in the first session whenever you try to access error will be generated saying that session has been killed. In the first session.
SQL> commit;
commit
*
ERROR at line 1:
ORA-00028: your session has been killed

Related Documents
Classes of Oracle Wait Events
What is enq: TX - row lock contention
New features in Oracle database administration in 11g
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT
http://arjudba.blogspot.com/2009/11/ora-01012-not-logged-on.html
http://arjudba.blogspot.com/2008/07/process-and-runtime-limits.html
http://arjudba.blogspot.com/2008/04/user-resource-limits-in-oracle.html
http://arjudba.blogspot.com/2010/01/causes-and-solutions-of-ora-00054.html

20 comments:

Urfan Alimov said...

Thanks mate

that was helpful

Fariha said...

Thank you arju for your great article. You are making the world really easy for me.

Ronald said...

Thanks for the clear answer. I hate being patient but it sounds like what the right thing to do :)

Jeremiah Wilton said...

Killing sessions, is not polite to users, and only helps in an inactive system where there isn't a new session obtaining a lock on the table every second. Instead, you can use a couple of other approaches. How about a PL/SQL proc that loops trying until it can get a lock? In 11g we have ddl_lock_timeout, which is designed to solve the issue. I just blogged about solving this problem here: Blue Gecko Remote DBA Blog: ORA-00054.

Regards,
Jeremiah Wilton
Blue Gecko, Inc.

richard said...

Hi Arju,
Your notes have helped me several times recently so thanks for sharing and keep up the good work.

Anonymous said...

Great! tanx so much!

castiglioni.raffaello said...

thank. perfect solution.

Raffaello

Anonymous said...

thanks mate that was really helpfule

BarbW said...

Excellent example. Fixed my problem. Thanks!!!

appu said...

Thanks keep it up...

Anonymous said...

Thank You so much Arju,Your explanation have have saved my time and i dont need to contact my system DBA to solve ORA-00054 error.

regards
Deepa

Anonymous said...

Thanks Mate

It was really helpful

shahid mohamed.s said...

SHAHID


HI,THANKS FOR UR KIND INFORMATION...
U SOLVED MY PROBLEM...THANK U...

shahid mohamed.s said...

hi thanks for ur information


....

Mohammed Taj said...

Nice post.

Meghana said...

Thanks a lot. Helped me with a problem I have been trying to resolve!

Anonymous said...

Really this example helps me a lot
my problem is fixed .

thank u very much

Subhakanta Mohapatra

Muhammad Abdul Halim said...

very nice one .

Regards
Muhammad Abdul Halim
http://halimdba.blogspot.com

Anonymous said...

Thanks a lot, boss. You saved my ass.

Anonymous said...

Cool Article!!!!!!! Thanks a lot buddy!!!!