Oracle 12.1 IMPDP Error Workaround

October 5, 2018 — Jeff Buturff

Oracle’s DATAPUMP system is a common way for DBAs to move databases from one instance to another.  For example, DATAPUMP can be used to move a Production database back into a TEST/DEV instance.  Or to provide vendors such as SSP with a copy of their Oracle database.  The DATAPUMP system has two commands:  EXPDP (for exporting a database to a file), and IMPDP (for importing that file into the Oracle database).

Recently, SSP Innovations and Esri have discovered a bug when using Oracle’s IMPDP Data Pump tool to import a database.  Only Oracle 12.1 is affected by this issue.

Issue

Running an IMPDP, receives the following error:

Because of the two highlighted error codes, the SDE.ST_SPATIAL_INDEX type doesn’t get imported.  Any subsequent spatial indexes fail to create, and will show the following error in the IMPDP logfile:

 

Doing a schema compare between a working Geodatabase and an imported database, there are two missing or corrupt types in the SDE schema:

Work-Around

To work-around this issue, you will need to run the IMPDP twice.

The first run of IMPDP should only import the SDE schema, like:

Running this command will still give you the two ORA- errors, as shown above.  We will need to repair the SDE schema:

From an ArcGIS Desktop installation, find the following four SQL scripts from C:\Program Files (x86)\arcgis\Desktop10.2\lib\.   Run these scripts as the SDE user, and supply the SDE argument:

Now that the SDE types are fixed, we need to recreate the spatial index type.  Run the following SQL command as SDE:

Your SDE schema should now be repaired and ready to go.

The last step is to run the IMPDP command again.  You can run a full IMPDP and exclude the SDE schema with the following syntax:

All your tables should be successfully imported, and more importantly, all your spatial indexes should be functioning correctly!

We Wrote the Book

The Indispensible Guide to ArcGIS Online

Download It for Free

What do you think?

Leave a comment, and share your thoughts

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


This site uses Akismet to reduce spam. Learn how your comment data is processed.