Validating and rebuilding large and fragmented indexes in 11i

There are various methods to identify large or fragmented indexes in 11i.

There are three possible ways to rebuild indexes :

i) Using ALTER INDEX ..REBULD ONLINE command
ii) Taking an export of database specifying ROWS=NO and then using the import with option INDEXFILE
iii) Using bde_rebuild.sql
You can download bde_rebuild.sql from metalink doc id 182699.1. Run this script in 11i instance using apps or system account

$sqlplus apps/apps @bde_rebuild.sql

This script will dynamically analyze and generates bde_rebuild_indexes.sql which rebuilds the required indexes.

This script has some optional parameters which script will ask when you run it:
– Owner of table (schema) : applsys (for example)
– Table name :
– Index name or Index Suffix
– Threshold between 20 and 80% : for this parameter default value is 50 which is sufficient for OLTP environment

The only care to be taken while running this script is : This script blocks DML commands on indexes being analyzed, including SELECT statements. Execute in Production during a low online user activity period. Blocking time lasts between few Sec to a few minutes, depending on index size.