Oracle Tips by Burleson
Occasionally, the DBA may be required to
rebuild indexes. In late Oracle version 7.3 and in Oracle8,Oracle8i,
and Oracle9i, the ALTER INDEX . . . REBUILD command can be used to
rebuild indexes on the fly. In releases prior to 8i, the rebuild was
done offline, restricting access to the table to read-only. The
ONLINE clause new in Oracle8i allows online rebuild of indexes. In
earlier versions (pre-7.3), the indexes have to be rebuilt using
drop and re-create scripts. If the scripts used to initially create
the system are available, this is a relatively simple matter. If the
scripts used to build the indexes are not available, or were never
created in the first place, the script IND_RCT9i.SQL (available on
the Wiley Web site) can be run to create a script that will
re-create existing, simple, nonpartitioned indexes when run. For
more complex indexes, use the DBMS_METADATA package available in
Oracle9i. The script will not re-create bitmapped or partition
indexes, though it can be modified to create bitmapped indexes by
utilizing the INDEX_TYPE column of the USER_INDEXES or DBA_INDEXES
views. The re-create script can also flag partition indexes by
utilization of the PARTITIONED column of the USER_INDEXES or
DBA_INDEXES views. To also rebuild partitioned indexes, the
USER_IND_PARTITIONS, USER_PART_KEY_COLUMNS, or DBA_IND_PARTITIONS
and DBA_PART_KEY_ COLUMNS views would have to be queried.
The DBA can use the ANALYZE command to
validate an index’s structures. The format of this command follows.
This data can help the DBA determine if a specific index has become
corrupted and must be rebuilt.
See Code Depot
The results are supplied to the DBA
on-screen and are placed in a view called index_stats, which is
dropped upon session exit.
In Oracle9i, the new package DBMS_METADATA
can be used to get DML to rebuild complex indexes. The DBMS_METADATA
function FETCH_DDL returns a CLOB data item that contains the text
to rebuild any database object.