Rebuild unusable index on a column of a table in Oracle schema

To rebuild unusable indexes on Oracle schema we can use this simple script.
This script first checks for all unusable indexes in the entire schema and then rebuild them to VALID state.

set serverout on
set head on
set echo on
declare
str varchar2(500);
begin for i in (select index_name,status from user_indexes where Status='UNUSABLE')
loop
str:='alter index ' || i.index_name || ' rebuild unrecoverable' ;
execute immediate str;
dbms_output.put_line('rebuilt index ' || i.index_name);
end loop;
end;
/

Sphere: Related Content