Question:  I need to drop an index in production, yet when I try to drop index, I get an ORA-00054 error that says that there are updates against the table:

DROP INDEX XXXX

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

How can I drop a production index while the system is running?

Answer:  First, see these notes on 11g using read only tables.

You need to schedule the ?drop index? for when there is low activity and notify the end-users about the scheduled maintenance.

Before 11g, you can mark the tablespace read-only for the duration of the drop index:

alter tablespace tab_ts read only

In 11g, you can mark the table as read-only to prevent updates during an index drop:

alter table mytab read only

You can verify that the table is read-only with this command:

select
   table_name, read_only
from
   dba_tables
where
owner = ?myowner? and table_name = ?mytab?;

As an alternative to making the table read-only, you can create a temporary trigger to prevent updates:

create or replace trigger

   tabl_read_only

before

   insert or update or delete

on mytab

begin

raise_application_error (-999999, 'Table mytab is undergoing maintenance.  Try again later.');

end; /

Oracle read-only table performance

Because Oracle does not have the additional overhead of maintaining internal consistency, there may be a small, but measurable reduction in resource consumption. 

The Oracle 10g Database Administrator's Guide also suggests that read-only transactions will have faster performance:  

For better performance while accessing data in a read-only tablespace, you might want to issue a query that accesses all of the blocks of the tables in the tablespace just before making it read-only.

A simple query, such as SELECT COUNT (*), executed against each table will ensure that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for Oracle to check the status of the transactions that most recently modified the blocks.

These notes on Oracle read-only tablespaces and Performance suggest that super high-activity system may see gains.