Title: Reorganizing the Indexes to a Different Tablespace

Author: T.V. Sarada Priya, a database administrator
      for Synergy Login Systems Sdn., Bhd., in Kuala Lumpur, Malaysia.

      Generally, if you have 100's of indexes in your database and you want to reorganize all
      indexes to a different tablespace, you would:

      1. Prepare a script for all the Disabling Primary Key constraints which has to be
      executed before dropping index

      2. Prepare a 'CREATE INDEX ...' script pointing to a different Tablespace.

      3. Disable or Drop the Constraints if they are Primary Key(USING INDEX)

      4. Drop all the Indexes from the Database.

      5. Run the script for enabling/creating the constraints back

      6. Run the 'CREATE INDEX ...' script

      This is a very time consuming process and there is a possibility that you might miss an
      index when writing the script.

      The better solution I found was to use the 'REBUILD' option of ALTER INDEX
      command.Assume you want to move all the indexes from 'OLDTBLESPACE' to
      'NEWTBLSPACE'. This will improve the performance of the index also, since
      'REBUILD' option is used.

      SQL> set head off
      SQL> set feedback off
      SQL> SPOOL INDREBUILD.SQL
      SQL> SELECT 'ALTER INDEX REBUILD ' || ind.index_name || ' TABLESPACE
      NEWTBLSPACE ;'      FROM user_indexes
           /

      [sample output]

      ALTER INDEX REBUILD SYS_C0010433 TABLESPACE NEWTBLSPACE;
      ALTER INDEX REBUILD SYS_C0010434 TABLESPACE NEWTBLSPACE;
      ALTER INDEX REBUILD SYS_C0010435 TABLESPACE NEWTBLSPACE;
      ALTER INDEX REBUILD SYS_C0010436 TABLESPACE NEWTBLSPACE;
      SQL> spool off

      [run the generated script file]


      SQL> @indrebuild.sql