English

How To: Move the st_spatial_index between tablespaces in Oracle

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