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: 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.
