Rebuilding invalid indexes in Oracle

I wrote many moons ago a post on finding invalid indexes in an Oracle database. I have since written a script to go through all the invalid indexes and attempt to rebuild them, going down to the partition or subpartition level. I have used this script many times and it appears to work as advertised.

It uses the all_indexes, all_ind_partitions, and all_ind_subpartitions so that it can interrogate all indexes it has permissions to view. It will attempt to rebuild the invalid indexes, and will report on any indexes it could see, but not rebuild. If run as a DBA account, it should be able to rebuild all invalid indexes in the system.

DECLARE

 schema_name all_tab_partitions.table_owner%TYPE;
 table_name all_tab_partitions.table_name%TYPE;
 index_partition_name all_tab_partitions.partition_name%TYPE;

 CURSOR invalid_indexes IS
  SELECT owner,
   index_name,
   table_owner,
   table_name,
   status,
   partitioned
   FROM all_indexes
   WHERE owner NOT IN ('SYS', 'SYSTEM')
    AND status != 'VALID'
    AND
    (
     status != 'N/A'
     OR index_name IN
     (
      SELECT index_name
       FROM all_ind_partitions
       WHERE status != 'USABLE'
        AND
        (
         status != 'N/A'
         OR index_name IN
         (
          SELECT index_name
           FROM all_ind_subpartitions
           WHERE status != 'USABLE'
         )
        )
     )
    );

 CURSOR tab_partitions IS
  SELECT partition_name,
   subpartition_count
  FROM all_tab_partitions
   WHERE table_owner = UPPER(schema_name)
    AND table_name = UPPER(table_name);

 CURSOR tab_subpartitions IS
  SELECT subpartition_name
   FROM user_ind_subpartitions
   WHERE partition_name = index_partition_name;

 successful_rebuilds NUMBER := 0;
 unsuccessful_rebuilds NUMBER := 0;

BEGIN

 FOR invalid_index IN invalid_indexes
 LOOP
  DBMS_OUTPUT.PUT_LINE('Rebuilding ' || invalid_index.owner || '.' || invalid_index.index_name);
  IF invalid_index.partitioned = 'NO' THEN
   BEGIN
    EXECUTE IMMEDIATE('ALTER INDEX ' || invalid_index.owner || '.' || invalid_index.index_name || ' REBUILD');
    successful_rebuilds := successful_rebuilds + 1;
   EXCEPTION
    WHEN OTHERS THEN
     unsuccessful_rebuilds := unsuccessful_rebuilds + 1;
     DBMS_OUTPUT.PUT_LINE('Unable to rebuild ' || invalid_index.owner || '.' || invalid_index.index_name || '.  ' || SQLERRM);
   END;
  ELSE
   schema_name := invalid_index.table_owner;
   table_name := invalid_index.table_name;
   FOR tab_partition IN tab_partitions
   LOOP
    IF tab_partition.subpartition_count = 0 THEN
     BEGIN
      EXECUTE IMMEDIATE('ALTER INDEX ' || invalid_index.owner || '.' || invalid_index.index_name || ' REBUILD PARTITION ' || tab_partition.partition_name || ' COMPUTE STATISTICS');
      successful_rebuilds := successful_rebuilds + 1;
     EXCEPTION
      WHEN OTHERS THEN
       unsuccessful_rebuilds := unsuccessful_rebuilds + 1;
       DBMS_OUTPUT.PUT_LINE('Unable to rebuild ' || invalid_index.owner || '.' || invalid_index.index_name || ', for partition ' || tab_partition.partition_name || '.  ' || SQLERRM);
     END;
    ELSE
     index_partition_name := tab_partition.partition_name;
     FOR tab_subpartition IN tab_subpartitions
     LOOP
      BEGIN
       EXECUTE IMMEDIATE('ALTER INDEX ' || invalid_index.owner || '.' || invalid_index.index_name || ' REBUILD SUBPARTITION ' || tab_subpartition.subpartition_name);
       successful_rebuilds := successful_rebuilds + 1;
      EXCEPTION
       WHEN OTHERS THEN
        unsuccessful_rebuilds := unsuccessful_rebuilds + 1;
        DBMS_OUTPUT.PUT_LINE('Unable to rebuild ' || invalid_index.owner || '.' || invalid_index.index_name || ', for subpartition ' || tab_subpartition.subpartition_name || '.  ' || SQLERRM);
      END;
     END LOOP;
    END IF;
   END LOOP;
  END IF;
 END LOOP;

 DBMS_OUTPUT.PUT_LINE(successful_rebuilds || ' indexes successfully rebuilt.');
 DBMS_OUTPUT.PUT_LINE(unsuccessful_rebuilds || ' indexes could not be rebuilt.');

END;