I work with Oracle more than I can remember, but every day I find something new.

This time I’m asked to drop one index on the production system, which is the simplest task you can get, right 구글 드라이브 일괄 다운로드?

I new there had to be a problem with that, otherwise someone else would do it.

In any case, the first attempt to drop the index (after verifying that it was safe to drop it) resulted in:

DROP INDEX <index_name>;

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

After the first attempt, I’ve decided to use new 11g feature – DDL_LOCK_TIMEOUT:

ALTER SESSION SET ddl_lock_timeout=30;

DROP INDEX <index_name>;

 

By executing this command, my session waits up to 30 seconds trying to acquire a lock Download windows 10 update.

Unfortunately, even 30 seconds was not enough due to some other issues that are out of scope of this article.

 

Lastly, I tried to change the state of the index to UNUSABLE Download the Windows Language Pack.

On several occasions, I wondered why this option existed after all, because the only operation you can do after putting the index in unusable state is either a DROP or REBUILD index aladdin 2019 다운로드.

Here is the answer.

When I executed the following command:

ALTER INDEX <index_name> UNUSABLE;

 

I was able to finally drop heavily used, but problematic index Download the Longchi edition.

DROP INDEX <index_name>;

 

In 11g you have another option – making an index INVISIBLE 태극 검 해.

While UNUSABLE index is ignored by CBO (Cost Based optimizer) and is not maintained by DML, INVISIBLE index is just ignored by CBO, which means in this case INVISIBLE option is not a way to go 서풍의 광시곡.

In 12c and on there are further DDL online options staying on your disposal, but as described case happened on 11g version of Oracle database, I couldn’t use those options Ring Mabel.

 

Summary:

As you can see, to perform even the most trivial tasks, you has to be familiar with the system in which you work and be prepared for surprises (plan B), especially in heavily used production environments jwc.

Under normal circumstances, ddl_lock_timeout, a new 11g feature of Oracle rdbms, would solve the problem.

In this case, ddl_lock_timeout did not help since more than 200 sessions locked the index I wanted to delete, so the last option to put the index in unusable state had to be used.


Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.