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;
