Importing a table with a ST_Geometry attribute using Oracle's import utility fails with Oracle error ORA-20091

Description

Using the Oracle Data Pump Import utility, or the original import utility, one might encounter the Oracle error ORA-20091.

The following example demonstrates using Oracle's Data Pump import utility and the error message that is returned when the table being imported contains an ST_Geometry attribute and st_spatial_index index whose SRID value and spatial reference properties do not exactly match.

In the following example, the map user is importing the buildings table from the dumpfile buildings.dmp:

C:\>impdp map/map tables=buildings directory=exp_imp_dir dumpfile=buildings.dmp
 
Import: Release 10.2.0.2.0 - Production on Tuesday, 22 January, 2008 16:38:00
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "MAP"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MAP"."SYS_IMPORT_TABLE_01":  map/******** tables=buildings directory=exp_imp_dir dumpfile=buildings.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MAP"."BUILDINGS"                           1.005 MB    3149 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/TABLE_DATA
. . imported "MAP"."S1_IDX$"                             157.5 KB    4205 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-20091: Import SRID does not match ST_Spatial_References SRID
Failing sql is:
BEGIN
SDE.st_type_export.validate_spref(1,-1196.334263,-1028.576111,1000000,0,1,0,1,4267);COMMIT; END;
Job "MAP"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 16:38:04

The ORA-20091 error indicates that the building's SRID value and properties do not match any existing SRID values in the sde.st_spatial_references table for the database where the import is being performed.

Cause

The cause for the error is because the importing table's spatial reference metadata must exactly match an existing spatial reference in the database where the import is being performed.

The metadata for the spatial reference is stored in the sde.st_spatial_reference table. Each entry in the sde.st_spatial_reference table has a unique SRID value and additional attributes that define the properties of the spatial reference.

To ensure the validity of the spatial index being imported, the metadata from the export file must be compared to the spatial reference metadata in the database where the import is being performed.

If the spatial reference does not exist, the Oracle error "ORA-20091: Import SRID does not match ST_Spatial_References SRID" displays and prevents the spatial index from being created.

Solution or Workaround

If this error is encountered during an import, the table containing the ST_Geometry attribute is still imported and the domain index table representing the st_spatial_index is still imported.

To create a spatial index on the table that was imported, the following steps must first be performed.

1. Drop the domain index table that was imported and is no longer valid in the imported database.
   
   The import utility prints messages to standard output when performing the import operation. These messages inform the user what tables are being imported and what step the import operation is currently performing.
   
   To identify the domain index table to drop, first locate the table name in the output that is generated during the import. Next, look for a table named S<#>_IDX$. This is the table to be dropped.
   
   The following output from an import operation demonstrates that S1_IDX$ is the table to be dropped.

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MAP"."BUILDINGS"                           1.005 MB    3149 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/TABLE_DATA
. . imported "MAP"."S1_IDX$"

SQL> DROP TABLE s1_idx$;

Table dropped.

2. Create a spatial reference that is applicable to the table's ST_Geometry attribute that was imported, if one does not already exist in the sde.st_spatial_references table.
   
   To create a new spatial reference, start ArcCatalog, connect to the ArcSDE instance and create a new feature dataset. When creating the feature dataset, specify a spatial reference by either selecting a spatial reference or importing a spatial reference for an existing data source.
   
   Once the feature dataset is created, it can be immediately deleted. The creation of the feature dataset creates a new spatial reference in the sde.st_spatial_references table and the spatial reference is not removed when the feature dataset is deleted.
   
   Next, obtain the SRID value from the sde.st_spatial_references table by selecting from the table where the sr_name equals the name of the spatial reference previously created.

SQL> SELECT srid, sr_name FROM sde.st_spatial_references
  2  WHERE sr_name = 'NAD_1983_StatePlane_Ohio_South_FIPS_3402_Feet';

      SRID SR_NAME
---------- -----------------------------------------------
        65 NAD_1983_StatePlane_Ohio_South_FIPS_3402_Feet

3. Update each ST_Geometry attribute to set the value for the new SRID value for the spatial reference. The ST_Geometry attribute SRID represents the spatial reference used by the st_spatial_index domain index. The attribute value must exist in the sde.st_spatial_references table.
   
   Using the value of the SRID from step 2, start SQL*Plus as the user who performed the import and update every row in the table that was imported.

SQL> UPDATE buildings t SET t.shape.srid = 65;

3149 rows updated.

SQL> COMMIT;

4. Create the spatial index on the ST_Geometry attribute. In SQL*Plus, as the user who imported the table, execute the CREATE INDEX command.

SQL> CREATE INDEX buildings_shape_idx
  2  ON buildings (shape) INDEXTYPE IS sde.st_spatial_index
  3  PARAMETERS ('st_srid=65 st_grids=200');

Index created.