OraFAQ Forum: SQL & PL/SQL » ORA-00054: resource busy and acquire with NOWAIT specified or timeout

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00054: resource busy and acquire with NOWAIT specified or timeout (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00054: resource busy and acquire with NOWAIT specified or timeout [message #604809]

Sat, 04 January 2014 04:57

cplusplus1
Messages: 58
Registered: October 2012
Location: usa

Member

When i try to truncate the table:
truncate table MDs_stage.T_LW_FBDETAIL;

I am getting the following error message, how to check which table object is locking.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout

Thanks a lot for the helpful info.

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout [message #604822 is a reply to message #604809]

Sat, 04 January 2014 05:47

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Your table is locked by anyone else using it.
You can check v$access view.

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout [message #604826 is a reply to message #604822]

Sat, 04 January 2014 05:56

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Note that "someone else" might be you (in a different session).

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout [message #604840 is a reply to message #604826]

Sat, 04 January 2014 07:54

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Some more detail.

TRUNCATE is a DDL statement (data definition). This is because it does a commit. That means that just the same as with ALTER TABLE, in order to execute DDL the database must acquire specific forms of locks on the table to ensure the table does not change inappropriately for the type of DDL being performed. For example, your expectation is that after TRUNCATE, the table will be empty. So the database must make sure that no one has an open insert/update/delete happening on the table. Thus the database checks first to see if someone has a parse lock on the table necessary to do an insert/update/delete. In your case someone did so you get this error. Someone else has either locked or indicated their intent to lock rows in the table or has otherwise acquired an incompatible lock on the table (a different DDL?) such that the DDL (TRUNCATE) cannot go forward.

Find out who this is and tell them to commit or finish whatever they are doing.

Kevin

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout [message #604844 is a reply to message #604840]

Sat, 04 January 2014 09:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

unCOMMITted DML was issued against MDs_stage.T_LW_FBDETAIL table.

Report message to a moderator

Previous Topic:	Need Static SQL
Next Topic:	How to send a Blob variable to a remote server

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 17:03:37 CDT 2024