麻雀虽小，五脏俱全：定期重建索引（oracle）

公司的所用的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;