Oracle Reindex Script

If you wish to reindex all of your Oracle tables in one fell swoop, try the following script:

begin
FOR rec IN (SELECT index_name FROM user_indexes) LOOP
EXECUTE IMMEDIATE 'begin dbms_stats.gather_index_stats(user,:b1); end;' using rec.index_name;
END LOOP;
end;

To check that it has worked correctly, execute the following and look at the last_analyzed column

select index_name, index_type, table_name, uniqueness, num_rows, last_analyzed from user_indexes