自动重建失效普通索引和分区索引

只为了做的更好
创建一个JOB调度下面过程就可以自动把失效索引重建

create or replace procedure re_index Authid Current_User is


   name_index varchar2(50);
   v_sql_reInd varchar2(1000);
   v_sql_reInd2 varchar2(1000);

   --ind_par varchar2(2000);




--cursor all_index(name varchar2) is select b.index_name from user_indexes a,user_ind_partitions b where a.INDEX_NAME=b.index_name and b.status='UNUSABLE' and b.index_name=name;
cursor all_index(name varchar2) is select b.index_name,b.partition_name,c.locality from user_indexes a,user_ind_partitions b,user_part_indexes c where a.INDEX_NAME=b.index_name and a.table_name=c.table_name and b.status='UNUSABLE' and b.index_name=name;


type par_ind is record(new_name varchar2(20),par_name varchar2(20),loc varchar2(50));
ind_par par_ind;

 cursor index_test is select a.index_name,a.status from user_indexes a where /*a.table_name= 'T_MXQ_L' and*/ a.status='UNUSABLE' or a.status='N/A';
type user_ind is record(name_in varchar2(20),stat_in varchar2(20));
all_ind user_ind;
begin


for all_ind in index_test  loop

   case all_ind.status

   when 'UNUSABLE' then

     v_sql_reInd := 'alter index ' || all_ind.index_name || ' rebuild online ';

    execute immediate (v_sql_reInd);


       --log_err('re_index'||all_ind.index_name || ' 重建成功 ');
   when 'N/A' then
     name_index:=all_ind.index_name;
    for ind_par in all_index(name_index) loop
      case ind_par.locality
        when 'LOCAL' then
   v_sql_reInd := 'alter index ' || ind_par.index_name || ' rebuild partition '||ind_par.partition_name ||' online ';
--alter index i_id_global rebuild partition p1 online nologging;
   execute immediate (v_sql_reInd);
        else
           v_sql_reInd := 'alter index ' || ind_par.index_name || ' rebuild online ';

   execute immediate (v_sql_reInd);
        end case;
       --log_err('re_index'||all_ind.index_name || ' 重建成功 ');
     end loop;

   end case;



  end loop;
exception
 when others then
     
      sg_log_err('re_index',sqlerrm);
       commit;
       --log_err('re_index',sqlerrm);
       dbms_output.put_line(sqlerrm);

end re_index;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29065182/viewspace-1622381/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29065182/viewspace-1622381/