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

Last Published: April 25, 2020


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.

Article ID:000009135

  • Legacy Products

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options