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

Description

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:

Cause

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. **

Workaround

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:
   
   

   Code:
   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.
   
   

   Code:
   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.
   
   

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

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