Rebuilding the Global Indexes

You need to rebuild all the global indexes of the tables whose partitions are dropped.

Procedure

To rebuild the global indexes:

  1. Connect to the ORMB database using a utility named SQL*Plus and the administrator's (for example, CISADM) credentials.
  2. Execute the following command: 
    Select 'ALTER INDEX' || INDEX_NAME || 'REBUILD PARTITION' || 
PARTITION_NAME || ';' from DBA_ind_partitions where status = 'UNUSABLE' 
AND INDEX_OWNER = 'CISADM';
    Note:

    This activity of rebuilding global indexes should be taken up during non-peak hours to avoid performance issues.

    Adequate disk space must be allocated for redo logs as considerable amount of redo is expected to be generated during the index rebuild activity.