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;