Is This Content Helpful?
We're glad to know this article was helpful.
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$;