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;