欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

批量rebuild索引

程序员文章站 2022-06-16 20:52:08
...

自己写了一个批量rebuild索引的存储过程: create or replace procedure sp_rebuild_indexas 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_na

自己写了一个批量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;