批量rebuild索引

create or replace procedure sp_rebuild_index
as 
 v_count number;
 cursor empCur is select owner,index_name from dba_indexes where status='UNUSABLE';
 v_sql varchar2(100);
 v_owner varchar2(20);
 v_index_name varchar2(50);
BEGIN
 select count(0) into v_count from dba_indexes where status='UNUSABLE';
 if v_count>0 then
 execute immediate 'alter session set skip_unusable_indexes=false';
 
 open empCur;
 
 loop
 fetch empCur into v_owner,v_index_name;
 v_sql:='alter index '||v_owner||'.'||v_index_name||' rebuild';
 execute immediate v_sql;
 exit when empCur%notfound; 
 end loop;
 
 close empCur;

 end if;
END;