HOW TO

Detect detached st_spatial_index domain index tables in Oracle

Last Published: April 30, 2021

Summary

Note:
ArcSDE software, including the application server, command tools, and SDK with C and Java APIs, was deprecated at ArcGIS 10.2.2 and is no longer distributed.

Instructions provided demonstrate how to detect any detached 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 detached 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 detached domain index tables causes no harm and can be safely dropped.

Procedure

The following SQL statement demonstrates how to detect any detached st_spatial_index domain index tables in the current schema and how to drop the tables:

Code:
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$';

TABLE_NAME
------------------------------
S258_IDX$
S235_IDX$

SQL> DROP TABLE s258_idx$;

Table dropped.

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.

Article ID:000009845

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Discover more on this topic