I need to drop an index from a table that is constantly used , read and write.
The DML's are locking the table and preventing the index drop (ORA-00054: resource busy and acquire with NOWAIT specified )
I tried to lock the table first - using 'lock table TAB1 in exclusive mode'
but when i ran the 'drop index' command , the drop (as all DDL's) first
commits and this frees the lock which cause again the ORA-00054:
resource busy and acquire with NOWAIT specified error.
I would appriciate any help .I can't take the DB or table or Application
writing to the table offline. I need something like 'create index IND1
online' for DROP but there is none as far as I know...
"It is requsted for the removal of an ORDER BY clause from a CPU consuming query.
The extra field enables me to remove it as the result set comes back ordered by the index"
It might do that today, but it is not guaranteed to do it tomorrow.
Oracle may be able to retrieve the records ordered using that index, but
I would leave it in your query if you are depending on the rows being
ordered. The CBO is smart enough to not actually sort the rows (i.e.
act on the ORDER BY clause) if it can get them sorted using the index,
but if it decides to use another access path for one of many reasons
your results will not be sorted without the ORDER BY.
Since the CBO seems to think that your existing index is useful (which
is why you cannot get the lock on it), your only option is to wait for a
period of really low activity on the database and try it then.
Never tried it this way and not able right now to setup the proper test conditions...
You might try marking the index unusable ALTER INDEX indexname UNUSABLE;
Then no transactions can use it, so you should be able to drop it.
Like I said, this may or may not work, but maybe worth a try.
I tried the 'alter session set ddl_lock=100' but sadly it didnt work as I am using oracle 9i.
I tend to agree with John regading the fact it is risky to leave the
ordering for the access path and index usage and decided to leave the
query with the order by clause.
It is still an anigma though that oracle lets you create an index online
but not to drop one - a fact that cause tables to hold old indexes that
can be dropped only when downtime is performed , which cause
Well, Oracle will let you
drop an index on-line, as long as there is no active query using that
index. Since you keep hitting locks when you try to drop that index, it
implies to me that it is almost constantly used. Which further implies
that it is , at least to the optimizer, a very useful index.
When you create an index, Oracle just needs to scan the table, just like
any query, and then build the index, it does not require any exclusive
locks so you will not have any problems. When you drop an index,
Oracle needs an exclusinve lock on the whole index, which it cannot get
if a query is using it. What would happen to an actve query using the
index if Oracle let you drop the index without getting the exclusive