Validating and rebuilding large and fragmented indexes in 11i
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.