Home » RDBMS Server » Server Administration » ORA-00054 resource busy and acquire with NOWAIT specified or timed expired (ORACLE 11.2.0.1 RAC of two node using oracle clusterware and Solaris10)
ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541530] Tue, 31 January 2012 23:50 Go to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
hi,
i want to drop a table by applying fol
SQL> drop table table1 purge;


and i get
ORA-00054 resource busy and acquire with NOWAIT specified or timed expired


This table is of 600 Gb in size and no more used and not involved in any logical and physical backup and do not referenced by application but still i am getting this error.i search for this error and found one advice which is to "Wait and retry" but i waited and then apply but still no progress.

kindly guide

Regards
janakors
Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541545 is a reply to message #541530] Wed, 01 February 2012 01:24 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You could try to find the accessing sessions by

select /*+ rule */ sid from  v$access where object='<YOUR TABLE NAME>' and owner='<YOUR OWNER>';
Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541560 is a reply to message #541545] Wed, 01 February 2012 03:11 Go to previous messageGo to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
hi,
thank you for your time.
Quote:

/*+ rule */


i didn't understand what does it mean.

kindly guide

Regards
jankors

Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541561 is a reply to message #541560] Wed, 01 February 2012 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Forget about it and remove it from the query, it's just a comment for you.

Regards
Michel

[Updated on: Wed, 01 February 2012 03:15]

Report message to a moderator

Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541562 is a reply to message #541560] Wed, 01 February 2012 03:15 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's a hint
Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541566 is a reply to message #541562] Wed, 01 February 2012 03:42 Go to previous messageGo to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
i apply the fol on sql prompt after logging " / as sysdba"
select  sid from  v$access where object='schema12.table1' and owner='schema12';

but i got no result and the query is going and going. i do not get my sql prompt. i started this query since last 40 min uptill now still processing, why is this behavior of the query? it should get me the session ID but it stuck or it will take time?

kindly advice

Regards
Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541567 is a reply to message #541566] Wed, 01 February 2012 03:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Object names and owners are stored in upper case in the data dictionary by default so that needs to be:
select sid from  v$access where object='SCHEMA12.TABLE1' and owner='SCHEMA12';
Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541569 is a reply to message #541567] Wed, 01 February 2012 03:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And the schema name won't be in the object name, so actually:
select sid from  v$access where object='TABLE1' and owner='SCHEMA12';

Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541580 is a reply to message #541569] Wed, 01 February 2012 04:25 Go to previous messageGo to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
well i have correct my query.thank you sir
now again i am having long wait but then i cancel the query and try to drop the tables so that was successful but i dint really understand it. i mean my purpose to solve the issue but in the middle , i want to learn as well which i am not sure, any ways if some one can refer me to a good reading so i will be thankful.

thanks orafaq and thank you all

looking for your advice


Regards

Janakors
Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541585 is a reply to message #541560] Wed, 01 February 2012 04:39 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
It is generally problematic to select from v$access. The hint RULE helps to get the result relative quickly (at least in some cases).

Regards
Leonid
Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541643 is a reply to message #541580] Wed, 01 February 2012 08:16 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
janakors wrote on Wed, 01 February 2012 05:25
well i have correct my query.thank you sir
now again i am having long wait but then i cancel the query and try to drop the tables so that was successful but i dint really understand it. i mean my purpose to solve the issue but in the middle , i want to learn as well which i am not sure, any ways if some one can refer me to a good reading so i will be thankful.


I really didn't understand anything you said here. Did the table drop? I think someone is not going to be happy because you said it was not in use, yet something was using it, hence the ORA-00054 error.
Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541869 is a reply to message #541643] Thu, 02 February 2012 22:39 Go to previous messageGo to next message
janakors
Messages: 232
Registered: September 2009
Senior Member
yes, table doped but let me say it again that it was not used by our application but even then i was getting this error but now next day i came and try to drop it so they got dropped which i am surprise too and also querying the same strange behavior.

Note that we have some 10 stations where this activity has to perform and it was successfully completed but in station 7 i had this problem. so problem is solved but no learning take place for me and may be anyone else.

Regards

janakors

Re: ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #547435 is a reply to message #541869] Wed, 14 March 2012 05:31 Go to previous message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi jana,

While i was searching for the solution to the resource busy wait error, i found your topic to be interesting.
Thought of replying to the issue you faced.

I faced the same error while trying to drop a table.Actually what happened is i ran one table import and
due to client connectivity got disconnected ,import got terminated in the middle.So when i try to drop the same table from database i get error "resource busy bla bla ..

So what action i took is i found which session id is blocking me to drop the table using below query

select sid from  v$access where object='TBL_TEMP' and owner='JACK';


Then found the serial# for the SID using v$session view using

select sid,serial#,status,program from v$session where status='ACTIVE' and sid='21'


Then killed it using
ALTER SYSTEM KILL SESSION '21,2825';


If the above scenario doesn't work , wait for the lock to get release and then drop the table.

I hope the solution which i provided might help you in future.

All the best!!!

[Updated on: Wed, 14 March 2012 05:32]

Report message to a moderator

Previous Topic: Archivelog Every 30 Seconds
Next Topic: server hang very often
Goto Forum:
  


Current Time: Thu Mar 28 13:11:27 CDT 2024