BUG
When using Oracle's Data Pump Import utility operation impdp with the remap_schema option, the import fails, creating the st_spatial_index.
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 indicates that the indextype "TOMB"."ST_SPATIAL_INDEX" does not exist.
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.
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.
Get help from ArcGIS experts
Download the Esri Support App