Compute, Validate and Rebuild Indexes in Oracle

Step 1: Save the below script in some particular location (suppose the path is D:\Exec_Indexing.sql) with extension .Sql

spool d:\Indexing_Script.sql;

select 'analyze index SCOTT.' || object_name || ' compute statistics;' || chr(10) || 'analyze index  SCOTT.' || object_name || ' validate structure;' || chr(10) || 'alter index  SCOTT.' || object_name || ' rebuild online;' from all_objects where owner='SCOTT' and object_type='INDEX';

spool off;

@d:\Indexing_Script.sql

Step 2: Run the above created .sql script (Exec_Indexing.sql) from SQL*PLUS or Command prompt as shown below:

SQL> @D:\ Exec_Indexing.sql

Output: in one go the above statement will automatically compute, Validate and Rebuild all the Indexes present within SCOTT Schema.