Frequently asked question
When creating a spatial index, st_spatial_index, in Oracle, it is possible the process may fail to create the index for various reasons; for example, a system failure or resource constraints.
It is possible Oracle will leave the index in a state where it still believes the index is being created, named 'INPROGRS'.
If, for any reason, the session building the index is no longer connected, but the index is still marked as 'INPROGRS', then the index will not be able to be dropped without using the FORCE option.
First, to detect if an index's state is 'INPROGRS', execute the following SQL statement in SQL*Plus in the schema that owns the table being indexed.
SQL> SELECT index_name, status FROM user_indexes WHERE table_name = 'US_STATES'; INDEX_NAME STATUS ------------------------------ -------- A139_IX1 INPROGRS R197_SDE_ROWID_UK VALID SYS_IL0000106192C00068$$ VALID
The output from the query shows the A139_IX1 index is still in the process of being created, 'INPROGRS'.
If this is not the case, the index can be dropped, but the Oracle error ORA_29868 displays.
SQL> drop index a139_ix1; drop index a139_ix1 * ERROR at line 1: ORA-29868: cannot issue DDL on a domain index marked as LOADING
Dropping the index requires using the FORCE option.
DROP INDEX a139_ix1 FORCE; Index dropped.
When the index is created without an error, the following values should be present in the user_indexes table.
Code: SQL> SELECT index_name, status, domidx_status, domidx_opstatus FROM user_indexes WHERE index_name = 'A139_IX1'; INDEX_NAME STATUS DOMIDX_STATU DOMIDX ------------------------------ -------- ------------ ------ A139_IX1 VALID VALID VALID
If the index failed on the execution of the create index statement, the following values will be present in the user_indexes table.
SQL> SELECT index_name, status, domidx_status, domidx_opstatus FROM user_indexes WHERE index_name = 'A139_IX1'; INDEX_NAME STATUS DOMIDX_STATU DOMIDX ------------------------------ -------- ------------ ------ A139_IX1 VALID VALID FAILED
Get help from ArcGIS experts
Download the Esri Support App