ERROR

Unable to drop an st_geometry spatial index that is marked as loading in Oracle

Last Published: April 25, 2020

Error Message

When dropping an st_geometry spatial index in Oracle, the following error message is encountered:

Code:
SQL> DROP INDEX shape_idx;
drop index shape_idx
*
ERROR at line 1:
ORA-29952: cannot issue DDL on a domain index partition marked as LOADING

Cause

When creating the st_geometry spatial index, the operation fails for some unknown reason and leaves the index status in a LOADING state.
In this example, the session is attempting to create a local spatial index on a partitioned table and specifies an invalid SRID in the parameters arguments.

Code:
SQL> CREATE INDEX shape_idx ON point_events (shape)
INDEXTYPE IS sde.st_spatial_index
PARAMETERS('st_srid=6 st_grids=.5') LOCAL;
create index shape_index on point_events (shape) indextype is sde.st_spatial_index
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20083: Parameter ST_SRID 6 does not exist in ST_SPATIAL_REFERENCES table.
ORA-06512: at "SDE.SPX_UTIL", line 836
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1258

Solution or Workaround

To drop an index requires using the FORCE option with the DROP INDEX command.

Code:
SQL> DROP INDEX shape_idx FORCE;

Index dropped.

    Article ID:000010805

    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