INDEX REORGANIZATION

Ever since Oracle implemented index rebuild in place option, it has made the job of managing and reorganizing indices easier for the DBA.  If one has disk space available to create a work tablespace for index reorganization, my script uses the rebuild
feature to make the process easier.  It rebuilds the selected indexes on the work tablespace first and then rebuilds them back
to the original or another tablespace, if desired.  There are many options provided in performing this routine task.  The
steps and prompts are as follows:
1) Make the changes you want to the index storage definition
(The only ones you are allowed to change are next, maxextents, pctincrease, freelists, freelist groups)
2) Invoke sqlplus and run this script and provide appropriate information to the prompts:
UNRECOVERABLE? (Y/N) – This tells the alter index rebuild statement to contain the unrecoverable clause.  KNOW THE RISK.
If it fails, you may lose the original index you were trying to rebuild, so you will have to know the index definition in order
to build this manually.

INDEX TABLESPACE TO BE REORGANIZED – Name of the Tablespace to be reorganized.

INDEX TABLESPACE TO USE FOR REORGANIZATION – Name of the work tablespace that will be used for reorganization.

EXTENTS GREATER THAN (0 for all) – Enter the number of extents that an index is in to select for rebuild/reorganization.  Enter 0 for all or any appropriate number like 25.

DELETE DEFAULT STATISTICS? (Y/N) – Alter index rebuild generates default statistics which can cause your rule based optimizer for Oracle Applications and Oracle v7.3 to perform unnecessary full table scans.  If you have Oracle Applications or do not want this default statistics to be collected, enter ‘Y’.

PARALLEL (N is for NOPARALLEL, Number for Degree) – Enter ‘N’ for non parallel rebuild of indexes.  If you have Parallel Query enabled, then you can enter the number of parallel query processes to invoke to rebuild the indexes.  Keep in mind that the initial extent will be the number of parallel query processes times the initial extent of the index.  So you may
want to use the next parameter to alter the initial extent so that this does not cause problems.

ALTER INITIAL EXTENT MODE (N,D,F,K,M) – You can alter the initial extent during the rebuild.  ‘N’ is no alteration. ‘D’ is for dividing, ‘F’ is for multiplying (factoring) the current initial by the number in the next parameter. ‘K’ is for KB in
the next parameter, and ‘M’ is for MB in the next parameter.

ALTER INITIAL EXTENT (1 OR appropriate number) – Enter 1 if ‘N’ was entered in the prior prompt. Enter a number that you want to divide the current initial extent by if ‘D’ was entered above (useful for parallel rebuild so that the initials are not too
large).  Enter a number that you want to increase the initial extent by a factor of (twice the current initial will be ‘F’ in
the prior prompt and ‘2’ for the current prompt).  If you specified ‘K’ or ‘M’, enter the number corresponding to the
kilobytes or megabytes that you want the initial extents to be.
 

ALTER DESTINATION TABLESPACE (N or TABLESPACE NAME) – If you want to change the location of the index to another tablespace than the source tablespace, enter the name of the tablespace.
Otherwise enter ‘N’.  PLEASE GRANT APPROPRIATE QUOTA PRIVILEGE TO THE INDEX OWNER ON THIS TABLESPACE FIRST IF YOU CHOOSE TO RELOCATE.
 

NUMBER OF SECONDS (INTEGER) TO SLEEP BETWEEN STMTS – Number of seconds to wait between execution of the individual alter index statements.  This is required so that the rdbms can perform appropriate cleanups.  Otherwise, you may receive “Unable to allocate…” error.  Typical value of ‘2’ has been sufficient.
 

NUMBER OF SECONDS (INTEGER) TO SLEEP BETWEEN TBLSP – Number of seconds to wait between switching from rebuilding to the work tablespace and rebuilding back to source or alternate destination.  This again allows the rdbms to perform appropriate cleanups.  Typical value is 1 more than above prompt.
 

SCHEMA – The schema which contains the indexes to be reorganized or rebuilt.

SCHEMA PASSWORD (not echoed) – Enter the schema password.

SYSTEM PASSWORD (not echoed) – Enter the system password.
 

3) Generates a spool file.  Please check for errors.  If error was encountered and UNRECOVERABLE was set to ‘N’ you should find the index in the work tablespace.  You have to manually move them, if needed.

4) Shows you the starting count of indexes and the ending count of how many indexes still meet the original selection criteria
and how many are in the destination tablespaces.
 

PLEASE TEST FIRST ON A TEST SYSTEM.

/*
Version: 2.0
Purpose:
    This routine reorganizes indexes for a tablespace
      The flow is as follows:
      Create a tablespace atleast as large as the largest index tablespace
      Make all the allowable changes you want to the storage parameters for
        the current index schema
      Prompts for:
             UNRECOVERABLE? (Y/N)
             INDEX TABLESPACE TO BE REORGANIZED
             INDEX TABLESPACE TO BE USED FOR REORGANIZATION
             SCHEMA
             EXTENTS GREATER THAN TO SELECT FOR REORG
             SCHEMA PASSWORD
             SYSTEM PASSWORD
             DELETE STATISTICS? (Y/N)
             PARALLEL ('N' NOPARALLEL) enter the degree (non zero)
             ALTDEST ('N' NO, Tablespace name to relocate to (Perform Quota
               alteration first!!))
             ALTER INITIAL MODE ('N' NO,'D' DIVISOR,'F' FACTOR,'K' KB,'M' MB)
             ALTER INITIAL QTY enter the number corresponding to above
               parameter
               ('1' NONE, appropriate number for others (digits)
             SLEEP1 Integer to specify the number of seconds to sleep
               between each index build statement to allow the RDBMS
               to cleanup the unused blocks (Otherwise it fails with
               unable to obtain initial extents, etc.)
             SLEEP2 Integer to specify the number of seconds to sleep
               between one index build and another

      This connects as system
      Grants quota unlimited on this tablespace to the owner
      Connects as the schema owner
      Generates and Executes 'ALTER INDEX  REBUILD ;'
      Generates and executes 'ALTER INDEX  REBUILD ;'
      If chosen for RULE based optimizer, then executes
       'DBMS_UTILITY.ANALYZE_SCHEMA('','DELETE');
      Connects as system
      Revokes quota unlimited on new_ts
*/

EXECUTE DBMS_OUTPUT.ENABLE (1000000);

ACCEPT UNREC PROMPT 'UNRECOVERABLE? (Know the risk?) (Y/N): '
ACCEPT OTS PROMPT 'INDEX TABLESPACE TO BE REORGANIZED: '
ACCEPT NTS PROMPT 'INDEX TABLESPACE TO USE FOR REORGANIZATION: '
ACCEPT GRTEXT PROMPT 'EXTENTS GREATER THAN (0 for all): '
ACCEPT STATDEL PROMPT 'DELETE DEFAULT STATISTICS? (Y/N): '
ACCEPT PARA PROMPT 'PARALLEL (N is for NOPARALLEL, Number for Degree): '
ACCEPT ALTIMD PROMPT 'ALTER INITIAL EXTENT MODE (N,D,F,K,M): '
ACCEPT ALTINI PROMPT 'ALTER INITIAL EXTENT (1 OR appropriate number): '
ACCEPT ALTDEST PROMPT 'ALTER DESTINATION TABLESPACE (N or TABLESPACE NAME): '
ACCEPT SLEEP1 PROMPT 'NUMBER OF SECONDS (INTEGER) TO SLEEP BETWEEN STMTS: '
ACCEPT SLEEP2 PROMPT 'NUMBER OF SECONDS (INTEGER) TO SLEEP BETWEEN TBLSP: '
ACCEPT SCHEMA PROMPT 'SCHEMA (OWNER): '
ACCEPT UPSWD PROMPT 'SCHEMA PASSWORD (not echoed): 'hide
ACCEPT SPSWD PROMPT 'SYSTEM PASSWORD (not echoed): 'hide

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
SET VERIFY OFF
SET TIMING ON

CONN SYSTEM/&SPSWD

ALTER USER &SCHEMA QUOTA UNLIMITED ON &NTS;

CONN &SCHEMA/&UPSWD

SET SERVEROUTPUT ON SIZE 1000000

spool ${ORACLE_SID}reorg.lst

SET HEADING OFF

SELECT 'START DATE/TIME: ' || to_char(SYSDATE, 'MM/DD/YY-HH:MI:SS')
FROM DUAL;

SELECT 'USER IS: '|| user ||' -- GLOBAL DATABASE NAME IS: '|| global_name
FROM global_name;

SELECT '*** RUN PARAMETERS ARE *** UNRECOVERABLE: '||upper('&UNREC'),
       '*** INDEX TS: '||upper('&OTS'),
       '*** WORK TS: '||upper('&NTS'),
       '*** GREATER THAN EXT: '||'&GRTEXT',
       '*** SCHEMA: '||upper('&SCHEMA'),
       '*** STATISTICS DELETE: '||upper('&STATDEL'),
       '*** PARALLEL (DEGREE): '||upper('&PARA'),
       '*** INITIAL EXTENT MODE: '||upper('&ALTIMD'),
       '*** INITIAL EXTENT: '||'&ALTINI',
       '*** ALTER DESTINATION TABLESPACE: '||upper('&ALTDEST'),
       '*** SLEEP (BETWEEN ALTER INDEXES): '||'&SLEEP1',
       '*** SLEEP (BETWEEN TABLESPACES): '||'&SLEEP2'
FROM DUAL;

-- Counting the number of indexes in this tablespace for the specified owner

SELECT 'Starting Number of Indexes in this Tablespace: ', count(*)
 FROM user_segments
 WHERE segment_type = 'INDEX'
 AND tablespace_name = upper('&OTS');

-- Counting the number of indexes to reorg

SELECT 'Number of Indexes to Rebuild: ', count(*)
 FROM user_segments
 WHERE segment_type = 'INDEX'
 AND tablespace_name = upper('&OTS')
 AND extents > &GRTEXT;

SET HEADING ON

-- Beginning of pl/sql

DECLARE
  CURSOR i_r_cur1 IS
     SELECT segment_name,
            bytes/1024/1024,
            ini_trans,
            max_trans,
            a.initial_extent,
            a.next_extent,
            a.min_extents,
            a.max_extents,
            a.pct_increase,
            a.freelists,
            a.freelist_groups,
            pct_free
     FROM user_segments a,
          user_indexes b
     WHERE a.tablespace_name = upper('&OTS')
     AND a.segment_name=b.index_name
     AND a.segment_type = 'INDEX'
     AND a.extents > '&GRTEXT'
     GROUP BY segment_name,
              bytes/1024/1024,
              ini_trans,
              max_trans,
              a.initial_extent,
              a.next_extent,
              a.min_extents,
              a.max_extents,
              a.pct_increase,
              a.freelists,
              a.freelist_groups,
              pct_free
     ORDER BY 2 DESC
    ;

  CURSOR i_r_cur2 IS
     SELECT segment_name,
            bytes/1024/1024,
            ini_trans,
            max_trans,
            a.initial_extent,
            a.next_extent,
            a.min_extents,
            a.max_extents,
            a.pct_increase,
            a.freelists,
            a.freelist_groups,
            pct_free
     FROM user_segments a,
          user_indexes b
     WHERE a.tablespace_name = upper('&NTS')
     AND a.segment_name=b.index_name
     AND a.segment_type = 'INDEX'
     GROUP BY segment_name,
              bytes/1024/1024,
              ini_trans,
              max_trans,
              a.initial_extent,
              a.next_extent,
              a.min_extents,
              a.max_extents,
              a.pct_increase,
              a.freelists,
              a.freelist_groups,
              pct_free
     ORDER BY 2 DESC
    ;

  statement1      VARCHAR2(255);
  statement2      VARCHAR2(255);
  cursor_handle   INTEGER;
  indexes_rebuilt INTEGER;
  uschema         VARCHAR2(30);
  icount          NUMBER := 0;
  ocount          NUMBER := 0;
  unrecover       VARCHAR2(14) := ' UNRECOVERABLE';
  parallel        VARCHAR2(12) := NULL;
  alterini        VARCHAR2(18) := NULL;
  alterdest       VARCHAR2(30) := upper('&ALTDEST');

BEGIN
  DBMS_OUTPUT.ENABLE (1000000);

  IF upper('&PARA') != 'N'
     THEN parallel := ' PARALLEL ';
          parallel := parallel||'&PARA';
  END IF;

  IF upper('&ALTIMD') = 'K'
  OR upper('&ALTIMD') = 'M'
     THEN alterini := ' INITIAL '||'&ALTINI'||'&ALTIMD';
  END IF;

  IF upper('&ALTDEST') != 'N'
     THEN alterdest := upper('&ALTDEST');
  END IF;

  IF upper('&ALTDEST') = 'N'
     THEN alterdest := upper('&OTS');
  END IF;

  DBMS_OUTPUT.PUT_LINE('*** Beginning Loop 1');
  DBMS_OUTPUT.PUT_LINE('------------');
  FOR i_r_cur1rec IN i_r_cur1
   LOOP
    BEGIN
      icount := icount + 1;
      DBMS_OUTPUT.PUT_LINE('#');
      DBMS_OUTPUT.PUT_LINE('Loop '||icount||'/1');

    IF upper('&ALTIMD') = 'D'
     THEN alterini := ' INITIAL '||round(i_r_cur1rec.initial_extent/&ALTINI);
    END IF;

    IF upper('&ALTIMD') = 'F'
     THEN alterini := ' INITIAL '||round(i_r_cur1rec.initial_extent*&ALTINI);
    END IF;

      statement1 :='ALTER INDEX '
                  ||i_r_cur1rec.segment_name||
                  ' REBUILD TABLESPACE '
                  || '&NTS' ||
                  parallel||
                  ' PCTFREE '
                  ||i_r_cur1rec.pct_free||
                  ' INITRANS '
                  ||i_r_cur1rec.ini_trans||
                  ' MAXTRANS '
                  ||i_r_cur1rec.max_trans||
                  ' STORAGE (NEXT '
                  ||i_r_cur1rec.next_extent||
                  alterini||
                  ' MINEXTENTS '
                  ||i_r_cur1rec.min_extents||
                  ' MAXEXTENTS '
                  ||i_r_cur1rec.max_extents||
                  ' PCTINCREASE '
                  ||i_r_cur1rec.pct_increase||
                  ' FREELISTS '
                  ||i_r_cur1rec.freelists||
                  ' FREELIST GROUPS '
                  ||i_r_cur1rec.freelist_groups||
                  ' )';

    IF upper('&UNREC')= 'Y'
       THEN statement1 := statement1||unrecover;
    END IF;

    DBMS_OUTPUT.PUT_LINE('1-> '||icount||' - '||statement1);
    DBMS_OUTPUT.PUT_LINE(' ');
    cursor_handle := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursor_handle, statement1, DBMS_SQL.NATIVE);
    indexes_rebuilt := DBMS_SQL.EXECUTE(cursor_handle);
    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
    DBMS_LOCK.SLEEP(&SLEEP1);

    EXCEPTION

    WHEN OTHERS
    THEN
    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
    DBMS_OUTPUT.PUT_LINE('$$$$ Error Occured in - Phase 1 - '||statement1||'*');
    DBMS_OUTPUT.PUT_LINE('Error: '||to_char(SQLCODE));
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    RAISE;
    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('*** Ending Loop 1');
  DBMS_LOCK.SLEEP(&SLEEP2);
  DBMS_OUTPUT.PUT_LINE('*** Beginning Loop 2');
  DBMS_OUTPUT.PUT_LINE('------------');
  FOR i_r_cur2rec IN i_r_cur2
   LOOP
    BEGIN
      ocount := ocount + 1;
      DBMS_OUTPUT.PUT_LINE('#');
      DBMS_OUTPUT.PUT_LINE('Loop '||ocount||'/2');
      statement2 :='ALTER INDEX '
                  ||i_r_cur2rec.segment_name||
                  ' REBUILD TABLESPACE '
                  ||alterdest||
                  parallel||
                  ' PCTFREE '
                  ||i_r_cur2rec.pct_free||
                  ' INITRANS '
                  ||i_r_cur2rec.ini_trans||
                  ' MAXTRANS '
                  ||i_r_cur2rec.max_trans||
                  ' STORAGE (NEXT '
                  ||i_r_cur2rec.next_extent||
--                  alterini||
                  ' MINEXTENTS '
                  ||i_r_cur2rec.min_extents||
                  ' MAXEXTENTS '
                  ||i_r_cur2rec.max_extents||
                  ' PCTINCREASE '
                  ||i_r_cur2rec.pct_increase||
                  ' FREELISTS '
                  ||i_r_cur2rec.freelists||
                  ' FREELIST GROUPS '
                  ||i_r_cur2rec.freelist_groups||
                  ' )';

    IF upper('&UNREC')= 'Y'
       THEN statement2 := statement2||unrecover;
    END IF;

    DBMS_OUTPUT.PUT_LINE('2-> '||ocount||' - '||statement2);
    DBMS_OUTPUT.PUT_LINE(' ');
    cursor_handle := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursor_handle, statement2, DBMS_SQL.NATIVE);
    indexes_rebuilt := DBMS_SQL.EXECUTE(cursor_handle);
    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
    DBMS_LOCK.SLEEP(&SLEEP1);

    EXCEPTION

    WHEN OTHERS
    THEN
    DBMS_SQL.CLOSE_CURSOR(cursor_handle);
    DBMS_OUTPUT.PUT_LINE('$$$$ Error Occured - Phase 2 - in '||statement2||'*');
    DBMS_OUTPUT.PUT_LINE('Error: '||to_char(SQLCODE));
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    RAISE;
    END;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('*** Ending Loop 2');

    IF upper('&STATDEL') = 'Y'
       THEN
          uschema := upper('&SCHEMA');
          DBMS_OUTPUT.PUT_LINE('*** ');
          DBMS_OUTPUT.PUT_LINE('Deleting Statistics...');
          DBMS_UTILITY.ANALYZE_SCHEMA(uschema,'DELETE');
    END IF;

END;
/

-- Counting number of indexes

SET HEAD OFF

SELECT 'Number of Indexes That Still Meets the Original Criteria: ', count(*)
 FROM user_segments
 WHERE segment_type = 'INDEX'
 AND tablespace_name = upper('&OTS')
 AND extents > &GRTEXT;

-- Counting the number of indexes in this tablespace for the specified owner

SELECT 'Ending Number of Indexes in '||'&OTS'||' Tablespace: ', count(*)
 FROM user_segments
 WHERE segment_type = 'INDEX'
 AND tablespace_name = upper('&OTS');

SELECT 'Number of Indexes in '||'&ALTDEST'||' Tablespace: ', count(*)
 FROM user_segments
 WHERE segment_type = 'INDEX'
 AND tablespace_name = upper('&ALTDEST')
 AND upper('&ALTDEST') != 'N';

SELECT 'END DATE/TIME: ' || to_char(SYSDATE, 'MM/DD/YY-HH:MI:SS')
FROM DUAL;

spool off

SET HEAD ON ECHO ON FEEDBACK ON

CONN SYSTEM/&SPSWD

ALTER TABLESPACE &OTS COALESCE;

ALTER TABLESPACE &NTS COALESCE;

ALTER USER &SCHEMA QUOTA 0K ON &NTS;

EXIT;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips