oracle重建失效索引

此脚本首先找到连接用户失效的索引,并在线重建.

create or replace procedure index_rebuild as
  cursor mycur is
    select *
      from user_indexes
     where status = ‘UNUSABLE‘;
  myrec user_indexes%rowtype;
  vsql  varchar(100);
begin
  open mycur;
  while mycur%found
  loop
    fetch mycur
      into myrec;
    dbms_output.put_line(‘index   ‘ || myrec.index_name || ‘  is invalide ‘);
    vsql := ‘alter index ‘ || myrec.index_name || ‘ rebuild online‘;
    dbms_output.put_line(vsql);
    execute immediate vsql;
  end loop;
  close mycur;
end index_rebuild;

oracle重建失效索引

标签：