As applications age, you invariably have to perform some maintenance activities on existing indexes. You may need to rename an index to conform to newly implemented standards, or you may need to rebuild a large index to move it to a different tablespace that better suits the index’s storage requirements. The following list shows common tasks associated with index maintenance:

•     Renaming an index

•     Displaying the DDL for an index

•     Rebuilding an index

•     Setting indexes to unusable

•     Monitoring an index

•     Dropping an index

Each of these items is discussed in the following sections.

Renaming an Index

Sometimes you need to rename an index. The index may have been erroneously named when it was created, or perhaps you want a name that better conforms to naming standards. Use the ALTER INDEX … RENAME TO statement to rename an index:

SQL> alter index cust_idx1 rename to cust_index1;

You can verify that the index was renamed by querying the data dictionary:

SQL> select table_name ,index_name ,index_type ,tablespace_name ,status from user_indexes order by table_name, index_name;

Displaying Code to Re-create an Index

You may be performing routine maintenance activities, such as moving an index to a different tablespace, and before you do so, you want to verify the current storage settings. You can use the DBMS_METADATA package to display the DDL required to re-create an index. Here is an example:

SQL> set long 10000

SQL> select dbms_metadata.get_ddl(‘INDEX’,’CUST_IDX1′) from dual;

Here is a partial listing of the output:

SQL> CREATE INDEX “MV_MAINT”.”CUST_IDX1″ ON “MV_MAINT”.”CUST” (“CUST_ID”) PCTFREE 10 INITRANS 2 MAXTRANS 255 INVISIBLE COMPUTE STATISTICS

To show all index DDL for a user, run this query:

SQL> select dbms_metadata.get_ddl(‘INDEX’,index_name) from user_indexes;

You can also display the DDL for a particular user. You must provide as input to the GET_DDL function the object type, object name, and schema; for example,

SQL> select

dbms_metadata.get_ddl(object_type=>’INDEX’, name=>’CUST_IDX1′, schema=>’INV’)

from dual;

Rebuilding an Index

There are a couple of good reasons to rebuild an index:

•     Modifying storage characteristics, such as changing the tablespace

•     Rebuilding an index that was previously marked unusable to make it usable again

Use the REBUILD clause to rebuild an index. This example rebuilds an index named CUST_IDX1:

SQL> alter index cust_idx1 rebuild;

Oracle attempts to acquire a lock on the table and rebuild the index. If there are any active transactions that haven’t committed, Oracle will not be able to obtain a lock, and the following error will be thrown:

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

In this scenario, you can either wait until there is little activity in the database or try setting the DDL_LOCK_TIMEOUT parameter:

SQL> alter session set ddl_lock_timeout=15;

The DDL_LOCK_TIMEOUT initialization parameter instructs Oracle to repeatedly attempt to obtain a lock (for 15 seconds, in this case).

Transactions can block rebuilding indexes, but the index rebuild itself can block other transactions until the rebuild is complete. To avoid a rebuild from blocking transactions, use the key ONLINE:

SQL> alter index cust_idx1 rebuild online;

If no tablespace is specified, Oracle rebuilds the index in the tablespace in which the index currently exists. Specify a tablespace if you want the index rebuilt in a different tablespace:

SQL> alter index cust_idx1 rebuild tablespace reporting_index;

If you are working with a large index, you may want to consider using features such as NOLOGGING or PARALLEL, or both. This next example rebuilds an index in parallel, while generating a minimal amount of redo:

SQL> alter index cust_idx1 rebuild parallel nologging;