Simple SQL*Plus script for rebuild of indexes

In older versions of Oracle I sometimes faced problem of corupted or too fragmented indexes. In such case you need to rebuild them. But be careful this operation can be very time consuming and no one should work in database – if possible do it in restrict mode.

Here is very simple script for SQL*Plus which will do it:

</pre>
set define off
set serveroutput on
spool rebuild_dsu.log
prompt Starting RE-BUILD of existing indexes

BEGIN
FOR INDEX_RECORD IN (select owner||'.'||object_name as obj
from dba_objects where object_type = 'INDEX' and owner ='...here_schema_name...')
LOOP
execute immediate 'ALTER INDEX '||INDEX_RECORD.obj||' REBUILD';
END LOOP;
END;

/

Leave a Reply

Your email address will not be published. Required fields are marked *