只为了做的更好
创建一个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博客 ” ，链接：https://blog.itpub.net/29065182/viewspace-1622381/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
自动化RMAN脚本
请登录后发表评论
登录
全部评论
