| Problem with NOWAIT error when trying to drop index [message #264730] |
Tue, 04 September 2007 06:26  |
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 #264738 is a reply to message #264733] |
Tue, 04 September 2007 06:56  |
Frank
Messages: 7901
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 |
rleishman
Messages: 3728
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
