Home » SQL & PL/SQL » SQL & PL/SQL » Problem with NOWAIT error when trying to drop index
Problem with NOWAIT error when trying to drop index [message #264730] Tue, 04 September 2007 06:26 Go to next message
Ergates
Messages: 3
Registered: September 2007
Junior Member
Hello

First things first...
Oracle 10.2.0.2
OS: HPUX 11.23

We have a weekly mart refresh than runs over the weekend.
The first thing this does is to drop all the indexes on the tables.

Every few weeks we get a failure in the drop script.

The statement and error are
DROP INDEX RAS_TRADE_TCN_IDX;

ORA-00054 resource busy and acquire with NOWAIT specified

A number of things make this strange.

1) There are nearly 100 indexes dropped in the script, this is the only one that EVER fails. This includes another index on the same table.
2) This runs in the middle of the night, there should be no database activity. If there were, it should (logically) lock other indexes too.
3) There are no DML statements that run against this table. It is SQLLoader'ed once a week, and then never touched. So it can't be an uncommitted change.
4) The failure doesn't happen every week, just every now and then.
5) If you immediately re-run the script without taking any other action it will work.

There's nothing unusual about the index itself, here is the create statement for it
CREATE INDEX RAS_TRADE_TCN_IDX ON RAS_TRADE(RAS_TC_TCN) 
 COMPUTE STATISTICS
 PCTFREE 0 NOLOGGING
/

It's not a very big table (3 columns, 6000 rows), so nothing weird there either.

This has everyone totally stumped. Anyone got any ideas?
Re: Problem with NOWAIT error when trying to drop index [message #264733 is a reply to message #264730] Tue, 04 September 2007 06:32 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Oracle said,

ORA-00054:	resource busy and acquire with NOWAIT specified
Cause:	Resource interested is busy.
Action:	Retry if necessary.
Re: Problem with NOWAIT error when trying to drop index [message #264735 is a reply to message #264730] Tue, 04 September 2007 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
First things first...
Oracle 10.2.0.2
OS: HPUX 11.23

Thanks for that.

As Arju posted, there is someone holding the resource.
Query v$lock and/or v$access to know what are the accesses on the object.

Regards
Michel
Re: Problem with NOWAIT error when trying to drop index [message #264738 is a reply to message #264733] Tue, 04 September 2007 06:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Arju wrote on Tue, 04 September 2007 13:32
Oracle said,

ORA-00054:	resource busy and acquire with NOWAIT specified
Cause:	Resource interested is busy.
Action:	Retry if necessary.


I really expect that the original poster was able to find this himself. Especially since he summed up a list of reasons excluding things that could cause this.
Re: Problem with NOWAIT error when trying to drop index [message #264886 is a reply to message #264738] Tue, 04 September 2007 22:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Are the indexes dropped serially? ie. One at a time in a single script? Or is the load spread over 2 or more scripts?

Are there any foreign keys that include that column?

10g will gether statistics for you overnight. It's not overlapping with that process is it?

Sometimes these messages can be misleading. It seems like it failed acquiring a lock on either the index or the table, but Oracle does all sorts of crazy stuff in the background with DDL - perhaps it is trying to lock some other object or data row. Clearly I'm guessing, but a good experiment would be to lock the table in exclusive mode first and then drop the index. If the drop still fails, it's not because of the table or the index.

Ross Leishman
Re: Problem with NOWAIT error when trying to drop index [message #266279 is a reply to message #264886] Mon, 10 September 2007 06:19 Go to previous message
Ergates
Messages: 3
Registered: September 2007
Junior Member
The indexes are dropped serially, all in one script.

We don't use any Foreign or Primary keys on our database at all.

We have the stats-gathering thing turned off, so it won't be doing anything at night.

We've put in some error trapping code, so that next time this fails, it will immediately go and check for table-locks etc etc. Just a matter of waiting for it to go over again, then we should have some more information.
Previous Topic: Copy table with index.
Next Topic: How to "union" values in two columns from the same table?
Goto Forum:
  


Current Time: Wed Dec 07 03:19:53 CST 2016

Total time taken to generate the page: 0.17104 seconds