Oracle database platform is a popular database to host ESRI ArcGIS Geo-Database, so I am going here to list the best approach to migrate your database for ESRI ArcGIS SDE:

From the source database, use expdp to fully export the Oracle database using SDE account (you need to temporary grant “DBA” role for such action):

expdp sde/XXXX dumpfile=GISDB_FULL%U.DMP directory=EXP_DIR FULL=Y PARALLEL=4 compression=all logfile=FULL_EXP_LOG.log

 

Now, in the target database start importing ONLY SDE schema first (SDE schema holds GIS data metadata and management scripts so its very important that this schema gets imported successfully):

impdp sde/XXXX dumpfile=GISDB_FULL%U.DMP directory=EXP_DIR SCHEMAS=SDE PARALLEL=4 logfile=SDE_IMP.log

After the import, check the log file and make sure that all objects were imported successfully…..you can compare the number of SDE schema objects by executing the following query in both source & target database:

SQL> select count(*) from dba_objects where OWNER=’SDE’;

In my case some objects were not created successfully !

One failed object is indextype ST_SPATIAL_INDEX , to view the definition of the index type from the source database:

SQL> set long 100000

SQL> set head off

SQL> set echo off

SQL> set pagesize 0

SQL> set verify off

SQL> set feedback off

SQL> SELECT dbms_metadata.get_ddl(‘INDEXTYPE’,’ST_SPATIAL_INDEX’,’SDE’) FROM dual;

 

I will execute the definition of the indextype:

 

CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX” FOR

“SDE”.”ST_BUFFER_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, NUMBER

),

“SDE”.”ST_CROSSES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER)

,

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER,

VARCHAR2),

“SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),

 

“SDE”.”ST_EQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_ORDERINGEQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_OVERLAPS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_RELATE” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),

“SDE”.”ST_TOUCHES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),

“SDE”.”ST_WITHIN” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”)

USING “SDE”.”ST_DOMAIN_METHODS”

WITH LOCAL RANGE PARTITION;

 

The following error will be thrown:

ORA-29829: implementation type does not exist

After digging….the following two “types” are missing:

ST_DOMAIN_METHODS

ST_DOMAIN_STATS

 

Take their SQL definition from the source database and create them in the destination, and try re-creating the indextype ST_SPATIAL_INDEX..it should be created successfully.

 

Now after ensuring SDE schema is fully mimicking the source database, start importing other schemas (user geodatabase schemas) and test it using ArcGIS Desktop.