You can have various methods to identify the large or fragmented indexes in 11i or you can just configure the Oracle Alert to receive a mail. This alert is scheduled to check the situation periodically say daily at mid night.
The alert ‘Indexes too large or fragmented‘ is pre-defined alert.
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
I would prefer the last method. You can download the bde_rebuild.sqlscript 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.
Execution of this script have some optional parameters which script will ask when you run it:
— Owner of table (schema) <optional> : 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
I would prefer to run the script for required schema (identify required schema from alert) separately for every schema.
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.