CODEWITHSUNDEEP
oracle-databaseoracle11g
Ankit
Ankit

Reputation: 1250

Dropping an index while DML operations in progress

I am working on a script to create an index online on one of the tables that is constantly being accessed by the application. I would like to know, if there is a way for me to drop the index online as well just in case if a back out is required.

I am using Oracle Database 11g 11.2.0.4.0

The reason why I am asking this is because if I try to delete the index without taking an exclusive lock it will give ORA-00054 - resource busy. The oracle doc says I can use online for 12c, is there a way to achieve this in 11g as well? DROP INDEX [ schema. ] index [ ONLINE ] [ FORCE ] ;

Any suggestions?

Upvotes: 1

Views: 9546

Answers (1)

vercelli
vercelli

Reputation: 4767

You should try ddl_lock_timeout (I guess the table won't be locked forever):

DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue

alter session set ddl_lock_timeout = 1000000;
drop index idxName;

Maybe you should consider changing it to INVISIBLE first:

ALTER INDEX idName INVISIBLE;

Upvotes: 3

Related Questions