Set serverout on size 1000000
declare
cursor idx_cur IS
select owner, index_name from dba_indexes where status = 'UNUSABLE';
v_string varchar2(400);
begin
dbms_output.put_line('These indexes are in unusable.');
for idx_rec in idx_cur
loop
exit when idx_cur%notfound;
dbms_output.put_line(idx_rec.owner||' '||idx_rec.index_name);
v_string := 'alter index '|| idx_rec.owner ||'.'|| idx_rec.index_name||' rebuild;';
dbms_output.put_line(v_string);
dbms_output.put_line(chr(10));
execute immediate v_string ;
end loop;
end;
/
declare
cursor idx_cur IS
select owner, index_name from dba_indexes where status = 'UNUSABLE';
v_string varchar2(400);
begin
dbms_output.put_line('These indexes are in unusable.');
for idx_rec in idx_cur
loop
exit when idx_cur%notfound;
dbms_output.put_line(idx_rec.owner||' '||idx_rec.index_name);
v_string := 'alter index '|| idx_rec.owner ||'.'|| idx_rec.index_name||' rebuild;';
dbms_output.put_line(v_string);
dbms_output.put_line(chr(10));
execute immediate v_string ;
end loop;
end;
/