HOW TO

Move the st_spatial_index between tablespaces in Oracle

Last Published: April 25, 2020

Summary

Instructions provided describe how to move an st_geometry attribute's spatial index to a new tablespace. Reasons that may force the movement of a spatial index to a new tablespace could be space management or resource contention between multiple segments on the same physical disk.

To move a spatial index between tablespaces, use the Oracle ALTER TABLE command in SQL*Plus.

Procedure

The following example demonstrates how to move a table's spatial index to a new tablespace using the Oracle ALTER TABLE command in SQL*Plus.

The example's objective is to move the street table's spatial index from its current tablespace to a new tablespace named strt_idx_tbs.
  1. The first step is to identify the street table's unique geometry identifier. The geom_id is used as the unique identifier for the geometry and its spatial index's table name.

    Code:
    SQL> SELECT geom_id
    2 FROM sde.st_geometry_columns
    3 WHERE table_name = 'STREET'
    4 AND owner = USER;

    GEOM_ID
    ----------
    102

  2. Next, execute the ALTER TABLE command to move the spatial index table to its new tablespace strt_idx_tbs. The name of the spatial index table to move is the combination of S<geom_id>_IDX$. After performing the move operation, a view can be queried to verify the table now exists in the new tablespace.

    Code:
    SQL> ALTER TABLE s102_idx$ MOVE TABLESPACE strt_idx_tbs;

    Table altered.

    SQL> SELECT tablespace_name
    2 FROM user_indexes
    3 WHERE index_name = 'S102$_IX1';

    TABLESPACE_NAME
    ------------------------------
    STRT_IDX_TBS

Article ID: 000009505

Software:
  • 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