Oracle 批量重建索引


一、创建过程

create or replace procedure p_rebuild_all_index
    (tablespace_name
in varchar2)
as
    sqlt
varchar(200);
begin

    
for idx in (select index_name, tablespace_name, status from user_indexes where tablespace_name=tablespace_name and status='VALID' and temporary = 'N') loop
    
begin
            sqlt :
= 'alter index ' || idx.index_name || ' rebuild ';
           --dbms_output.put_line(idx.index_name);
           --dbms_output.put_line(sqlt);
           
EXECUTE IMMEDIATE sqlt;
           
--错误后循环继续执行。
            EXCEPTION
           
WHEN OTHERS THEN
                 dbms_output.put_line(SQLERRM);
     
end;              
     
end loop;
end;

二、测试方法

declare
     --表空间名称
tablespace_name varchar2(100);
begin
tablespace_name:='dddd';
p_rebuild_all_index(tablespace_name);
end;

相关内容