Rebuild Indexes

Here's s a neat script I wrote for re-building indexes. One of the apps I look after has a requirement of having indexes stored in a tablespace called INDEXES. This is the script I wrote for doing this programmatically.

This script can be easily modified to re-build all indexes, or all non SYS/SYSTEM indexes, or just invalid indexes. It can also be wrapped in a package, and then called by a job.

The cool thing about this script is the way it uses a BULK COLLECT :).


DECLARE
   sql_stmt                      VARCHAR2 ( 2000 ) DEFAULT NULL;

   TYPE sql_stmt_array_type IS TABLE OF sql_stmt%TYPE;

   sql_stmt_array                sql_stmt_array_type DEFAULT NULL;

   CURSOR cursor1
   IS
      SELECT 'ALTER INDEX ' || a.owner || '.' || a.index_name
             || ' REBUILD TABLESPACE INDEXES' sql1
        FROM dba_indexes a, dba_objects b
       WHERE a.owner IN
                ( 'SCHEMA_OWNER1'
                 ,'SCHEMA_OWNER2')
         AND a.index_type = 'NORMAL'
         AND a.TEMPORARY LIKE 'N'
         AND a.owner = b.owner
         AND a.index_name = b.object_name
         AND b.object_type = 'INDEX'
         AND a.tablespace_name != 'INDEXES';
BEGIN
   OPEN cursor1;

   FETCH cursor1
   BULK COLLECT INTO sql_stmt_array;

   FOR i IN 1 .. sql_stmt_array.COUNT
   LOOP
      sql_stmt := sql_stmt_array ( i );

      EXECUTE IMMEDIATE sql_stmt;
   END LOOP;

   CLOSE cursor1;
END;
/