English

Bug: Importing an Oracle export file with the remap_schema option fails

Description

When using Oracle's Data Pump Import utility operation impdp with the remap_schema option, the import fails, creating the st_spatial_index.

Code:
C:\exp_imp>impdp tomb/tomb TABLES=buildings dumpfile=exp_imp_dir:buildings.dmp REMAP_SCHEMA=sde:tomb

Import: Release 10.2.0.2.0 - Production on Tuesday, 22 January, 2008 9:28:55

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 "TOMB"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TOMB"."SYS_IMPORT_TABLE_01": tomb/******** TABLES=buildings dumpfile=exp_imp_dir:build.dmp REMAP_SCHEMA=sde:tomb
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TOMB"."BUILDINGS" 54.820 KB 12 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/TABLE_DATA
. . imported "TOMB"."S12_IDX$" 7.820 KB 19 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-29833: indextype does not exist
Failing sql is:
CREATE INDEX "TOMB"."A3_IX1" ON "TOMB"."BUILDINGS" ("SHAPE") INDEXTYPE IS "TOMB"."ST_SPATIAL_INDEX" PARAMETERS ('st_grids=500 st_srid=6')PARALLEL 1
Job "TOMB"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at 09:28:58


The error is indicating that the indextype "TOMB"."ST_SPATIAL_INDEX" does not exist.

Cause

The cause of this problem is specific to when one attempts to import a table with a st_geometry attribute and spatial index from an export file where the data owner is the SDE user and the target import schema is a user other than the SDE user.

It appears the Oracle Data Pump Import utility is performing a global replace of the source schema name with the target schema name when the remap_schema option is specified.

Changing the owner of the st_spatial_index causes the CREATE INDEX statement to fail because the INDEX TYPE, sde.st_spatial_index, is owned by the SDE schema, not the target schema.

Workaround

ArcGIS recommends user data should not be created in the ArcSDE SDE schema.

When all user data is created in schemas other than the SDE schema, one would never encounter this error when exporting and importing tables when using Oracle's Data Pump Import utility.

If the data does reside in the ArcSDE SDE schema and must be exported, to avoid the error during the import operation, drop the spatial index on the table before performing the export.

After performing the export and import operations, re-create the st_spatial_index on the st_geometry attribute. This can be accomplished by either using ArcCatalog or executing the CREATE INDEX statement with SQL.