How To: Detect orphaned st_spatial_index domain index tables in Oracle
Instructions provided demonstrate how to detect any orphaned st_spatial_index domain index tables in Oracle.
In any Oracle schema that has created tables with a ST_Geometry attribute, there may be occurences of orphaned st_spatial_index domain index tables when a table containing a ST_Geometry attribute has been dropped or domain index tables have been imported using Oracle's Data Pump Export or Import utility.
The orphaned domain index tables causes no harm and can be safely dropped.
The following SQL statement demonstrates how to detect any orphaned st_spatial_index domain index tables in the current schema and how to drop the tables:
SQL> SELECT table_name
2 FROM user_catalog
3 WHERE table_type = 'TABLE'
4 AND table_name NOT IN
5 (SELECT 'S' || geom_id || '_IDX$'
6 FROM sde.st_geometry_columns
7 WHERE owner = USER)
8 AND table_name like 'S%_IDX$';
SQL> DROP TABLE s258_idx$;
The st_spatial_index domain index naming convention is S<geom_id>_IDX$, where the value for geom_id is the unique geometry identifier stored in the sde.st_geometry_columns table.