此存储过程用于重建失效索引
create or replace
procedure index_rebuild
as
cursor mycur is
select * from user_indexes
where status='UNUSABLE';
myrec user_indexes%rowtype;
vsql varchar(100);
begin
open mycur;
while mycur%found
loop
fetch mycur into myrec;
dbms_output.PUT_LINE('index '||myrec.index_name|| ' is invalide ');
vsql :='alter index '|| myrec.index_name ||' rebuild';
dbms_output.put_line(vsql);
execute immediate vsql;
end loop;
close mycur;
end index_rebuild;
procedure index_rebuild
as
cursor mycur is
select * from user_indexes
where status='UNUSABLE';
myrec user_indexes%rowtype;
vsql varchar(100);
begin
open mycur;
while mycur%found
loop
fetch mycur into myrec;
dbms_output.PUT_LINE('index '||myrec.index_name|| ' is invalide ');
vsql :='alter index '|| myrec.index_name ||' rebuild';
dbms_output.put_line(vsql);
execute immediate vsql;
end loop;
close mycur;
end index_rebuild;
![b.gif](http://b1.cnc.qzone.qq.com/ac/b.gif)
转载于:https://blog.51cto.com/383610/1345548