Rebuild all unusable indexes (and partitioned indexes):
The Script:-- Rebuild all unusable indexes (and partitioned indexes)
-- Analyze only if index to be rebuilt already has statistics
declare
isanalyzed date;
begin
execute immediate 'alter session set sort_area_size = 10000000';
for crs in (select * from dba_ind_partitions where status = 'UNUSABLE') loop
isanalyzed := crs.last_analyzed;
execute immediate 'alter index '||crs.index_owner||'.'||crs.index_name||' rebuild partition '||crs.partition_name;
if isanalyzed is not null then
execute immediate 'analyze index '||crs.index_owner||'.'||crs.index_name||' partition ('||crs.partition_name||') estimate statistics';
end if;
end loop;
for crs in (select * from dba_indexes where status not in ('VALID','N/A')) loop
isanalyzed := crs.last_analyzed;
execute immediate 'alter index '||crs.owner||'.'||crs.index_name||' rebuild';
if isanalyzed is not null then
execute immediate 'analyze index '||crs.owner||'.'||crs.index_name||' estimate statistics';
end if;
end loop;
end;
/
