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;