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


Importing an Oracle export file containing a table with an ST_Geometry attribute, the command fails with the errors: ORA-39779, ORA-31693 and ORA-02354.

The following example demonstrates using the Oracle Data Pump 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. The remap_schema argument informs the import utility to map the schema from the original data owner "rob" to the importing user "tb".

C:\expdir> impdp tb/tb TABLES=buildings dumpfile=expdir:buildings.dmp remap_schema=rob:tb

The import fails and reports the following error message:

"Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

ORA-31693: Table data object "MAP"."CITIES_ST" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39779: type "SDE"."ST_GEOMFROMTEXT" not found or conversion to latest version is not possible".

The table is imported but now rows are inserted into the imported table.


The cause of the problem is a type object identifier mismatch for the sde.st_geomfromtext object exported from the origin database and the sde.st_geomfromtext type object identifier in the destination import database.

When exporting a table from one Oracle database to a second Oracle database and the table contains a user defined type for an attribute (for example: ST_Geometry), the type's object identifier value in the origin and destination databases must have the same value. If the object identifier values are not identical, Oracle considers the objects different and cannot import the row data.

The error is being encountered on the sde.st_geomfromtext object because it is the first ST_Geometry object created when installing ArcSDE in the Oracle database.

Solution or Workaround

To be able to import an Oracle export file from another Oracle database, the SDE schema containing the ST_Geometry attribute must be identical. To be identical requires first exporting and importing the SDE schema from the origin database to the destination database.

The following example demonstrates how to use Oracle's Data Pump to export the SDE schema and import the SDE schema into a second Oracle database.

The first step is to export the SDE schema specifying the schema to export and the dumpfile.

C:\expdir> expdp sde/sde schemas=sde dumpfile=expdir:sde_schema.dmp

To import the SDE schema, specify the schema and dumpfile directory containing the dumpfile.

C:\expdir> impdp sde/sde schemas=sde dumpfile=expdir:sde_schema.dmp

Once the SDE schema is present in the destination Oracle database, proceed with importing the export file containing the table.

C:\expdir> impdp tb/tb TABLES=buildings dumpfile=expdir:buildings.dmp remap_schema=rob:tb

Importing the SDE instance into the destination Oracle database also imports all the existing metadata from the origin database, such as existing registered tables and layers. To remove this metadata for objects that do not exist in the destination database, use ArcCatalog and delete the objects.