Automic Workload Automation

  • 1.  ORACLE INDEX REBUILD

    Posted Jul 24, 2017 11:45 AM
    You Asked

    How can I move an index into another tablespace?

    And we said …

    With a standard "alter index rebuild" Oracle will set an exclusive lock, effectively blocking all DML that might happen during the rebuild period. However, this is mitigated with Oracle parallel index rebuilding. 

    The rules for identification of candidates for index coalescing/rebuilding depend on your specific index state. See MOSC notes 989186.1, 122008.1, 989093.1 for Oracle's suggestions on when to coalesce/rebuild indexes.

    ALTER INDEX [schema.]index REBUILD  
         [PARAMETERS ('rebuild_params [physical_storage_params]' ) ] 
         [{ NOPARALLEL | PARALLEL [ integer ] }] ;
          
    PHYSICAL_STORAGE_PARAMS

    Determines the storage parameters used for rebuilding the spatial index data table. A spatial index data table is a regular Oracle table with a prescribed format. Not all physical storage parameters that are allowed in the STORAGE clause of a CREATE TABLE statement are supported.

    For further information please check the Oracle documentation or go in touch with your DBA.