Rebuilding the Global Indexes

Once the eligible table partitions are dropped, you need to rebuild all global indexes of such tables.

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 off 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.