English

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

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.