How can a spatial index be recovered when its status is marked as 'INPROGRS' in Oracle?

Answer

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