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.

Popularity: 7% [?]

Share This