| ORA-00054 resource busy and acquire with NOWAIT specified or timed expired [message #541530] |
Tue, 31 January 2012 23:50  |
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 #541566 is a reply to message #541562] |
Wed, 01 February 2012 03:42  |
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 #541580 is a reply to message #541569] |
Wed, 01 February 2012 04:25 |
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 #541643 is a reply to message #541580] |
Wed, 01 February 2012 08:16 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
janakors wrote on Wed, 01 February 2012 05:25well 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 |
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 |
 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
