Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




Oracle Tips by Burleson 

Rebuilding Indexes

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.

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks