oracle存储过程批量重建索引.doc

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; 定期重建索引（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 ????????????