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 ;
(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 ;
oracle 存储过程批量重建索引。
测试方法:
declare
-- 表空间名称
tablespace_name varchar2 ( 100 );
begin
tablespace_name: = ' dddd ' ;
p_rebuild_all_index(tablespace_name);
end ;
-- 表空间名称
tablespace_name varchar2 ( 100 );
begin
tablespace_name: = ' dddd ' ;
p_rebuild_all_index(tablespace_name);
end ;