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!
What do you think?