Oracle索引重建

//111111//
公司的所用的oracle数据库，因为数据增、删比较频繁，导致索引产生碎片，性能下降，并占用空间不能有效释放。由于目前暂时找不到合适的DBA对数据库进行优化，于是写了一个简单的脚本来定期重建所有的索引。本文提到的脚本创建一张表用来记录索引重建的日志，建立一个存储过程，并建立一个 job 来每 7 天调用一次该存储过程。


-- 因为系统中很对表的数据变化比较频繁，导致索引空间膨胀，系统性能下降
-- 因此需要定期重建系统中的索引，以优化性能，回收空间
-- 这项维护性工作通过 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;