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:
· 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.
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
Use Oracle to alter the storage parameters for these objects, or use the MOVE command to move the objects to a different tablespace.
- 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>';
- 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.
- Rebuild the s<n>$_ix2 index to move this index using the same GEOM_ID.
Code:
ALTER INDEX s<n>$_ix2 REBUILD TABLESPACE <tablespace>;
- Repeat the process for each spatial index that needs to be altered.