Bug: Spatial index segments for ESRI's spatial type in Oracle do not use DBTUNE storage parameters


The spatial index segments for a feature class, using ESRI's spatial type for Oracle, do not use the storage parameters from the DBTUNE table.

The spatial index creates the following objects in the database:

· S<n>_IDX$
· S<n>$_IX1
· S<n>$_IX2

Where <n> is the GEOM_ID value in the SDE.ST_GEOMETRY_COLUMNS table for the specified table.

S<n>_IDX$ is the name of the Indexed Organized Table (IOT), S<n>_IX1 is the primary constraint for the IOT, and S<n>$_IX2 is the index for the sp_id attribute.


This is a known limitation.

When the Index Organized Table and index are created, the storage parameters in the DBTUNE are ignored; therefore, each object is created in the user's default tablespace.

** This has been fixed in ArcSDE 9.2 Service Pack 4. **


Use Oracle to alter the storage parameters for these objects, or use the MOVE command to move the objects to a different tablespace.

  1. Connect to the database using SQL*Plus as the owner of the feature class, and determine the GEOM_ID of the feature class that needs to be moved from the SDE.ST_GEOMETRY_COLUMNS table:

    SELECT table_name, geom_id
    FROM sde.st_geometry_columns WHERE table_name = '<table_name_wanted>';

  2. Using the output from the previous query, move the associated spatial index for the feature classes.

    ALTER TABLE s<n>_idx$ MOVE TABLESPACE <tablespace>;

    Where <n> refers to the GEOM_ID generated from step 1.

  3. Rebuild the s<n>$_ix2 index to move this index using the same GEOM_ID.

    ALTER INDEX s<n>$_ix2 REBUILD TABLESPACE <tablespace>;

  4. Repeat the process for each spatial index that needs to be altered.