oracle 存储过程批量重建索引

create o r r eplace p rocedure p_rebuild_all_index
(tablespace_name i n v archar2)
as
s qlt v archar(200);
begin
f or i dx i n(select i ndex_name, t ablespace_name, s tatus f rom u ser_indexes w here t ablespace_name =tablespace_name a nd s tatus='VALID'a nd t emporary='N') l oop
b egin
s qlt :='alter i ndex '||i dx.index_name ||'r ebuild ';
d bms_output.put_line(idx.index_name);
d bms_output.put_line(sqlt);
E XECUTE I MMEDIATE s qlt;
--错误后循环继续执行。

E XCEPTION
W HEN O THERS T HEN
d bms_output.put_line(SQLERRM);
e nd;
e nd l oop;
end;
declare
--表空间名称
t ablespace_name v archar2(100);
begin
t ablespace_name:='dddd';
p_rebuild_all_index(tablespace_name);
end;
方法2：
定期重建索引（oracle）文章来源：本站原创更新时间：2009-9-23 13:47:38
公司的所用的oracle数据库，因为数据增、删比较频繁，导致索引产生碎片，性能下降，并占用空间不能有效释放。

由于目前暂时找不到合适的DBA对数据库进行优化，于是写了一个简单的脚本来定期重建所有的索引。

本文提到的脚
本创建一张表用来记录索引重建的日志，建立一个存储过程，并建立一个job 来每7 天调用一次该存储过程。

声明：因为我不是DBA，个人感觉这个的方法不正规（不是best practice），仅供没有更好办法的时候参考。

-----------------------------------------
-- 因为系统中很对表的数据变化比较频繁，导致索引空间膨胀，系统性能下降-- 因此需要定期重建系统中的索引，以优化性能，回收空间
-- 这项维护性工作通过Oracle 的job 进行调度
-- 建立一张表，存放索引重建日志
CREATE TABLE tmMTNLog (
fLogDate char ( 19 ),
fLogMsg varchar2 ( 4000 )
);
-- 首先创建一个存储过程，该存储过程重建所有的索引
CREATE OR REPLACE procedure mtn_rebuild_all_idx
as
cursor indexCursor is
select * from user_indexes where table_owner = 'XXXXX' and index_type = 'NORMAL' ; --请将XXXXX替换为oracle用户名
indexRow indexCursor %ROWTYPE;
sqlText varchar2 ( 1024 );
begin
open indexCursor ;
loop
fetch indexCursor into indexRow ;
exit when indexCursor %NOTFOUND;
sqlText := ' alter index ' || indexRow . index_name || ' rebuild ' ;
BEGIN
execute immediate ( sqlText );
insert into tmMTNLog ( fLogDate , fLogMsg ) values( sysdate , 'rebuild index success:' || indexRow . index_name );
EXCEPTION
WHEN OTHERS THEN
insert into tmMTNLog ( fLogDate , fLogMsg ) values( sysdate , 'rebuild index fail:' || indexRow . index_name );
END;
end loop;
end;
/
-- 然后建立一个Oracle 任务，这个任务每隔七天调度一次mtn_rebuild_all_idx 这个存储过程
-- 请注意，Oracle 的任务创建脚本不能多次执行，因为每次执行都会生成一个新的任务，如果要修改，请先删除原有任务
-- 因为Oracle 中用编号表示任务，所以脚本不清楚该任务是否已经存在，无法做到自动删了新建
DECLARE
X NUMBER ;
BEGIN
SYS .DBMS_JOB.SUBMIT
(
job => X
, what => 'ITIMS.MTN_REBUILD_ALL_IDX;'
, next_date => TRUNC ( SYSDATE + 7 )
,interval => 'TRUNC(SYSDATE+7)'
, no_parse => FALSE
);
END;。