PROBLEM
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".
Code:
C:\expdir> impdp tb/tb TABLES=buildings dumpfile=expdir:buildings.dmp remap_schema=rob:tb
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.
Code:
C:\expdir> expdp sde/sde schemas=sde dumpfile=expdir:sde_schema.dmp
Code:
C:\expdir> impdp sde/sde schemas=sde dumpfile=expdir:sde_schema.dmp
Code:
C:\expdir> impdp tb/tb TABLES=buildings dumpfile=expdir:buildings.dmp remap_schema=rob:tb
Note:
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.
Get help from ArcGIS experts
Download the Esri Support App