English

Problem: Importing an Oracle export file with an ST_Geometry attribute encounters the Oracle error ORA-39117

Description

When importing an Oracle export file containing a table with an ST_Geometry attribute, the command fails with Oracle error ORA-39117.

The following example demonstrates using the Oracle Import utility to import an Oracle export file named buildings.dmp. The dumpfile contains one table named buildings, which contains the attribute ST_Geometry and a st_spatial_index index on the ST_Geometry attribute.
 

Code:
C:\expdir> imp tb/tb file=buildings.dmp full=y

The import fails and reports the following error message:
 
Error:   
"ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "TB"."BUILDINGS" ("OBJECTID" NUMBER(38,0) NOT NULL ENABLE, "SUBTYPEID" NUMBER(10,0), "BUILDNG_ID" NVARCHAR2(20), "SHAPE" SDE.ST_GEOMETRY
ORA-39083: Object type TABLE failed to create with error:
ORA-22809: nonexistent attribute"

Cause

Attempting to import a table with the ArcSDE ST_Geometry attribute requires the type and domain index to be present in the SDE schema. Without the SDE schema and type, the Oracle import is unable to create the table with the ST_Geometry attribute.

Solution or Workaround

Importing an Oracle export file containing a table with an sde.st_geometry attribute requires the presence of the SDE schema and the ArcSDE ST_Geometry attribute and st_spatial_index domain index.

Either install ArcSDE, or import an Oracle export containing the SDE schema and type, prior to performing the import.