Script to drop and recreate indexes causing error

I have a script that I run monthly that drops and recreates all of our indexes. I did this to improve performance. However, when I look at the log file, I'm finding that some of our indexes did not get recreated. Here is the error message:

ORA-00054: resource busy and acquire with NOWAIT specified. 

I looked up this error and it sounds like the index still has ahold of a lock while it's being created. So when it tries to create the next index, the lock hasn't been released from the one prior. Should I do a commit after each index creation? Any advice would be helpful.

You don't say if the error occurs when attempting to drop the index or when attempting to recreate it. If it occurs on the drop attempt, it most likely means that the index you're trying to drop is being used by another session. If it occurs on the recreate, it's because Oracle takes out an exclusive lock on the table while the index is being built, and no DML is allowed on the table during that time. If there is any other sort of lock on the table, Oracle cannot obtain an exclusive lock and the operation will time out after 5 minutes.

DROP INDEX and CREATE INDEX are both DDL statements, and DDL statements perform an automatic commit on completion.

If you must rebuild your indexes, your best bet would be to use ALTER INDEX...REBUILD ONLINE. This operation rebuilds the index rather than dropping and recreating, and the ONLINE keyword allows DML during rebuild. The most important question here, though, is whether the rebuilds are really improving your performance. Do you have any evidence that they are? If not, then there's no reason to rebuild your indexes periodically.

Dig Deeper on Oracle database performance problems and tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.