如何进行批量索引重建?

--################################################################
--Description: 重建索引过程
--Author: zzq
--Date: 2006-12-6
--Version: 0.1
--################################################################    

--建立表analyze_index,用来存储index分析后的信息
 create table analyze_index
 (name VARCHAR2(30),
  btree_space number,
  height number,
  pct_used number,
  rate number)

--这个过程首先对索引进行分析,然后将临时表index_status的数据存储到analyze_index
中,然后对有问题索引进行重建,问题索引指的是btree_space > 8192
  and (height > 3 or pct_used < 75 
   or (del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100) > 20)具体请查询相关文档.重建后的索引要重新分析才可以插入到index_status表中.

create or replace procedure p_rebuild_index
as

  cursor m_cur is
  select distinct index_name from user_ind_columns;
  m_ind_name user_ind_columns.index_name%type;
  cursor m_cur1 is
  select * from analyze_index;
  i_sql varchar2(1000);
begin
i_sql:='truncate table  analyze_index';     
  EXECUTE immediate i_sql;
open m_cur;
loop
  fetch m_cur into m_ind_name;
  exit when m_cur%NOTFOUND;
  i_sql:='ANALYZE INDEX '||m_ind_name||' VALIDATE STRUCTURE';
  EXECUTE immediate i_sql;
  insert into analyze_index(name,btree_space,height,pct_used,rate)
  select name,btree_space,height,pct_used,del_lf_rows/(decode(lf_rows,0,1,lf_rows))
  from INDEX_STATS
  where btree_space > 8192
  and (height > 3 or pct_used < 75
        or (del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100) > 20)
;
  commit;
end loop;
close m_cur;
for m_analyze_ind in m_cur1
loop 
   if  true   then
   i_sql:='alter index '||m_analyze_ind.name||' rebuild'; 
   EXECUTE immediate i_sql;
   end if;
end loop;
i_sql:='truncate table  analyze_index';     
  EXECUTE immediate i_sql;
open m_cur;
loop
  fetch m_cur into m_ind_name;
  exit when m_cur%NOTFOUND;
  i_sql:='ANALYZE INDEX '||m_ind_name||' VALIDATE STRUCTURE';
  EXECUTE immediate i_sql;
  insert into analyze_index(name,btree_space,height,pct_used,rate)
  select name,btree_space,height,pct_used,del_lf_rows/(decode(lf_rows,0,1,lf_rows))
  from INDEX_STATS
  where btree_space > 8192
  and (height > 3 or pct_used < 75
        or (del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100) > 20)
;
  commit;
end loop;
close m_cur;
exception
  when others then
   if m_cur%isopen then
    close m_cur;
   end if;
   if m_cur1%isopen then
    close m_cur1;
   end if;
    rollback;
    DBMS_OUTPUT.PUT_LINE('[p_rebuild_index]  '||sqlerrm);  
end;
/
show errors;