Encountering Oracle error ORA-29903 when querying classes with st_geometry attributes

Error Message

If an application or user is executing a query against a table with an st_geometry attribute, and the query's access path is using the spatial index for the result set, it is possible to encounter the Oracle error ORA-29903 because the index owner has dropped the spatial index.

Code:
SQL> SELECT COUNT(*) FROM xycoords a, states_poly b
  2  WHERE sde.st_intersects(a.shape,b.shape) = 1;
select count(*) from xycoords a, ukpoly b
*
ERROR at line 1:
ORA-29903: error in executing ODCIIndexFetch() routine
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1204
ORA-06512: at "SYS.DBMS_SQL", line 323
ORA-06512: at "SDE.SPX_UTIL", line 3088
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 767

Cause

The cause of the error is because the DROP INDEX statement has dropped the domain index table, which was being accessed by an active query.

Unfortunately, Oracle does not apply any lock structures to domain indexes and the primary table object during the execution of a query to protect the underlying data structures (as they do when working with b*tree indexes and tables).

Implementing a locking mechanism within the domain index would add far too much complexity and overhead (performance impact) on every process which accesses the domain index.

Solution or Workaround