Invalid ROWIDs are present in the Oracle st_spatial_index domain index after importing a schema

Description

After importing a schema with a table containing an ST_Geometry attribute and st_spatial_index where the SDE schema was first imported (for example, importing an entire geodatabase that was exported from a production instance), each st_spatial_index domain index contains invalid ROWIDs.

Cause

When importing a schema from one Oracle instance to the next, and the schema contains tables with ST_Geometry attributes and st_spatial_index domain indexes, the imported domain index tables contain invalid ROWID values. The domain indexes are invalid because the table contains a ROWID attribute which is the pointer to each row in the table containing the ST_Geometry attribute. Since the values of the ROWID reference the ROWID values from the origin Oracle database, the domain index values in the destination database are invalid.

During the Oracle import operation, the process calls st_type_export's checkversion, validate_sidx, and validate_spref procedures to ensure the domain indexes that are being imported are valid spatial indexes referencing valid spatial references. The procedures validate the correctness of each spatial index being imported by ensuring the metadata is present in the SDE ST_Geometry metadata tables (sde.st_geometry_columns and sde.st_spatial_references). If the metadata is present, the domain index tables are considered to be valid and cannot be dropped and re-created. In this case, each spatial index row has invalid values for the sp_id attribute.

Solution or Workaround

Code:
SQL>DECLARE
  2   CURSOR all_indexes IS
  3     SELECT index_name FROM user_indexes WHERE ityp_name = 'ST_SPATIAL_INDEX';
  4
  5 BEGIN
  6   FOR each_idx IN all_indexes LOOP
  7     EXECUTE IMMEDIATE 'ALTER INDEX '||each_idx.index_name||' REBUILD';
  8   END LOOP;
  9 END;
 10 /

PL/SQL procedure successfully completed.