The database administrator’s responsibility includes rebuilding the indexes to improve performance of retrieving data and database searching.
 | Note: With Oracle Standard Edition or Oracle Standard Edition One, online reindexing is not available. You must therefore run the index_build script manually. |
To reindex the Oracle Enterprise Edition database
Determine the space requirements for rebuilding the database.
Configure the index_upkeep.cfg configuration file for the amount of time you want index_rebuild to run.
Some weeks, no indexes may be rebuilt, depending on the REBUILD_FREQUENCY parameter.
Determine a time when index_rebuild can be executed weekly.
The index rebuild is I/O intensive, so it should not run at the same time as other I/O intensive operations such as database backups and analyze-tables. Also, remember that although the MAXHOURS may be set to 1 or 2 hours in the configuration file, index rebuild always rebuilds a minimum of one index and, if it is a large index, this may take longer than the specified MAXHOURS time.
Add this weekly time to the crontab file or Scheduled Tasks.
After index_rebuild has completed successfully, back up the database.