Problem: Invalid ROWIDs are present in the Oracle st_spatial_index domain index after importing a schema
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.
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
Since each st_spatial_index domain index table is invalid, each st_spatial_index must be re-created. The easiest way to rebuild each st_spatial_index in the schema is to execute a basic PL/SQL anonymous block to rebuild each st_spatial_index in the current schema.
The following example demonstrates the PL/SQL anonymous block to execute in SQL*Plus for each imported schema. The PL/SQL uses a cursor named all_indexes to fetch each st_spatial_index name in the schema executing the code. For each st_spatial_index, the index is rebuilt.
2 CURSOR all_indexes IS
3 SELECT index_name FROM user_indexes WHERE ityp_name = 'ST_SPATIAL_INDEX';
6 FOR each_idx IN all_indexes LOOP
7 EXECUTE IMMEDIATE 'ALTER INDEX '||each_idx.index_name||' REBUILD';
8 END LOOP;
PL/SQL procedure successfully completed.
After rebuilding the indexes it is always recommended to gather new table and index statistics.