Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired (12c)
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659467] Wed, 18 January 2017 08:28 Go to next message
jaysorcale
Messages: 5
Registered: February 2008
Junior Member
Hi All,

Step 1;
DROP TABLE XX_RA_CUSTOMER_TRX_ALL_1

Step 2: Create custom table XX_RA_CUSTOMER_TRX_ALL_1

EXECUTE IMMEDIATE 'CREATE TABLE XX_RA_CUSTOMER_TRX_ALL_1
AS
SELECT CUSTOMER_TRX_ID,TRX_NUMBER,BILL_TO_CUSTOMER_ID,ORG_ID, CREATION_DATE FROM RA_CUSTOMER_TRX_ALL
WHERE ORG_ID = <Condition>'

Step 3: Create Index on the above created Table

EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX XX_RA_CUST_TRX_ALL_U21
ON XX_RA_CUSTOMER_TRX_ALL_1(CUSTOMER_TRX_ID)';

Problem:

I get the below error and this does not occur always.
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

Any help on the above will be of great help.
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659470 is a reply to message #659467] Wed, 18 January 2017 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
Your posted code will be a disaster in any multiuser application.

Rarely are temporary tables needed in Oracle; which is what you are attempting to do in the worst possible way.

stop issuing CREATE TABLE via EXECUTE IMMEDIATE!

if you TRUNCATE TABLE,then you do NOT need to CREATE INDEX

You shoot yourself in the foot & then complain that it hurts.

BTW I suspect that you really should be using Global Temporary Table, instead
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659471 is a reply to message #659470] Wed, 18 January 2017 09:22 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
The error is because someone has a lock on the table.
But as Blackswan says, creating tables on the fly in oracle is 99.999999999% of the time a bad idea.
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659473 is a reply to message #659471] Wed, 18 January 2017 09:44 Go to previous messageGo to next message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
cookiemonster wrote on Wed, 18 January 2017 10:22
The error is because someone has a lock on the table.
But as Blackswan says, creating tables on the fly in oracle is 99.999999999% of the time a bad idea.
LOL, I would add another 10 '9's onto the end of your percent.
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659474 is a reply to message #659467] Wed, 18 January 2017 09:55 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
You could quiesce the database before creating the table and unquiesce after launching the index creation. If your users would find that acceptable. Here's a description.
http://www.skillbuilders.com/Oracle/Oracle-Consulting-Training.cfm?category=blogs&tab=john-watsons-blog&node=2943

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659482 is a reply to message #659474] Wed, 18 January 2017 12:47 Go to previous messageGo to next message
joy_division
Messages: 4902
Registered: February 2005
Location: East Coast USA
Senior Member
John Watson wrote on Wed, 18 January 2017 10:55
You could quiesce the database before creating the table and unquiesce after launching the index creation. If your users would find that acceptable. Here's a description.
http://www.skillbuilders.com/Oracle/Oracle-Consulting-Training.cfm?category=blogs&tab=john-watsons-blog&node=2943

Wow, I never knew of this?

John, how different is this from the 11gR2 alter session set ddl_lock_timeout command?
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659484 is a reply to message #659473] Wed, 18 January 2017 22:31 Go to previous messageGo to next message
jaysorcale
Messages: 5
Registered: February 2008
Junior Member
Thanks All for the feedback. I agree EXECUTE IMMEDIATE is not a good option at all.

There is no lock on the table as it is not used by anybody and I am trying to understand why would it error with "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired " and also this would not happen always.

Thanks.
Jay
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659486 is a reply to message #659484] Wed, 18 January 2017 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
There is no lock on the table
Yes there is... or Oracle has a big bug and you should open a SR.

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659499 is a reply to message #659486] Thu, 19 January 2017 07:34 Go to previous messageGo to next message
Bill B
Messages: 1802
Registered: December 2004
Senior Member
If you are connected as a user with access to the sys views simply run the following command and it will show all the users locking your table
SELECT C.Owner,
       C.Object_name,
       C.Object_type,
       B.Sid,
       B.Serial#,
       B.Status,
       B.Osuser,
       B.Machine
  FROM V$locked_object A, V$session B, Dba_objects C
 WHERE     B.Sid = A.Session_id
       AND A.Object_id = C.Object_id
       AND A.Object_id = (SELECT Object_id
                            FROM Dba_objects
                           WHERE Owner = 'MY_SCHEMA' AND Object_name = 'MY_TABLE');
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659501 is a reply to message #659484] Thu, 19 January 2017 07:39 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
jaysorcale wrote on Wed, 18 January 2017 20:31
Thanks All for the feedback. I agree EXECUTE IMMEDIATE is not a good option at all.

There is no lock on the table as it is not used by anybody and I am trying to understand why would it error with "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired " and also this would not happen always.

Thanks.
Jay
It is most likely you causing the problem either via not issuing COMMIT previously or another session.

As POGO once said, "We have met the enemy, and they is us."
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #659502 is a reply to message #659482] Thu, 19 January 2017 07:46 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
joy_division wrote on Wed, 18 January 2017 18:47

<snip>
Wow, I never knew of this?

John, how different is this from the 11gR2 alter session set ddl_lock_timeout command?
Quiesce will block other sessions from doing anything, so that you can get your lock. The ddl_lock_tineout is kind of the reverse: you give up trying to get your lock, because you are blocked by other sessions. The real problem with using the tineout is that is if A gets a lock, then B gets a lock, then A releases his lock, then C gets a lock, then B releases his lock, and so on, your session will wait forever because there is always a TM lock on the table. With the quiesce, that doesn't happen. I would view them as complementary: use them together. I hadn't thought of that.
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #660276 is a reply to message #659502] Tue, 14 February 2017 01:43 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
Thanks everyone
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #660279 is a reply to message #660276] Tue, 14 February 2017 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is a little bit short when this "thank" is asked to you and given by you just to get another help.
Explain what you learn, what was the problem and how you solve it... to show you have clearly understood the issue and help future readers by your post.



Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #660288 is a reply to message #660279] Tue, 14 February 2017 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
@asliyanage - this doesn't appear to be one of your threads.
Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired [message #660708 is a reply to message #660288] Sat, 25 February 2017 01:42 Go to previous message
asliyanage
Messages: 60
Registered: January 2017
Member
sorry, i replied to this ,mistakenly
Previous Topic: Declare a cursor from the return of a procedure?
Next Topic: How to find No of Entries per week For consecutive week
Goto Forum:
  


Current Time: Mon Oct 22 00:30:26 CDT 2018