Import using Oracle datapump using different schema

4118
5
12-03-2015 07:44 AM
YvesThibault
New Contributor II

Hi,

I need to refresh a complete ArcGIS environment in a lab (data and structure).The SDE referentiel.  Note that in this lab environment, the ArcGIS environment already exist.  I simply drop the schema SDE and other schema that contains spatial data.

I want to use an existing environment (DEV) to refresh it.  I think I could quickly export/import the SDE schema and the schema containing the spatial data (AGRMGXD1).

Export (SID=SIGMAD)                                Import (SID=SIGMAI)

SDE                                                             SDE

AGRMGXD1                                                AGRMGXL1

After importing the data, I must update many tables of the SDE schema because, by example, the column "OWNER" of many tables contains the value "AGRMGXD1" and it must be "AGRMGXL1".  I made a script that search all the value of "AGRMGXD1" and replace it by "AGRMGXD1".

Finally, I start the ArcGIS service (esri_sde) and it worked.  On my Workstation, I started ArcCatalog and I connected to the schema AGRMGXL1 (it worked too).  I can see all the tables I imported.  But, when I selected a particular table, I saw the record number but I was unable to see the spatial data (in the preview panel).  I've got no error but it doesn't seem to work.

Did I forgot something to do ?    Is my way to do it make sense ?  Please, let me know.

Yves

NOTE: I know I can do it using SDE commands like sdeexport (from) and sdeimport (to)  but it will take much longer time to do it.  I would like to find a good and short receipe that work!

0 Kudos
5 Replies
George_Thompson
Esri Frequent Contributor

Adding other groups.....

Geodatabase

Enterprise GIS

--- George T.
0 Kudos
ChristianWells
Esri Contributor

Hi Yves,

It sounds like in your DEV environment the data owner is AGRMGXD1, however, after the export you want to change the owner to AGRMGXL1 in the SDE repository tables.

If the above is correct, editing the repository tables to change the owner of each table would not only be very difficult but also unsupported. The following tables, at minimum*, would need to be updated if you were going to change the owner:

GDB_ITEMS

COLUMN_REGISTRY

GEOMETRY_COLUMNS

LAYERS

TABLE_REGISTRY

*Depending on your environment, there may be other references to these objects some where else.

However, the most difficult part of changing the owner is that you would need to update the XML Definition and Documentation for every feature class. This data can be read using the SDEXMLTOTEXT function, however, you won't be able to update these XMLs because they are stored as BLOBs.

The only way I could see something like this working is if the the objects from "SIGMAI" was a perfect, identical match to "SIGMAD", with the exception of the owner. If that was the case, it may have been possible to retain the SDE schema in SIGMAI and during the datapump (IMPDP) operation, use the remap schemas flag.

Another possible workflow would be to create an empty geodatabase on SIGMAI and then only import the AGRMGXD1 schema using the remap_schemas flag to change it to  AGRMGXL1. Once this datapump is done, register all the objects with the geodatabase, using the Register with Geodatabase tool, which is easily scripted in Python.

EDIT:

At the end of the day, the best, least stressful, and supported way to accomplish this it to either leave the schema owner name as AGRMGXD1 or export all the data via ArcGIS (sdexport/sdeimport, ArcCatalog/ArcMap, ArcPy, etc)

YvesThibault
New Contributor II

Hi Christian,

Thank you very much!  You were very well understood my question .  Now, I understand why it didn't work before.

I already thought about your last suggestion (leave the schema owner name as AGRMGXD1 and use ArcCatalog to copy/paste to AGRMGXL1).  The problem is that I don't have enough space to do it at this time.  But I'm sure it would work.  I could do it if I export one table at a time but it will take a long time ... And it's not what I want to do!

As you suggest, I will try to create an empty geodatabase on SIMGA and only import the AGRMGXD1 schema using remap_schema option (and register with geodatabase tool)..

Thank's again!

Yves

0 Kudos
YvesThibault
New Contributor II

Hi Christian,

As I said yesterday, I tried to create an empty geodatabase and after, import the schema AGRMGXD1 in the instance SIGMAI using the remap_schema flag.

But I've got many errors during the import of AGRMGXD1.  All the tables seemed to be imported correctly but many errors appears about the sde type.  It looks that they are some references about AGRMGXD1.

Here is a part of the output (import):

...

. . import :
"AGRMGXL1"."MGX_UMU_UTILS_SOL_MUNCP"            0 KB       0 lignes

. . import : "AGRMGXL1"."MGX_ZCO_ZONE_COQLL_POLGN"           0 KB       0 lignes

. . import : "AGRMGXL1"."MGX_ZIN_ZONE_INTRV_POLGN"           0 KB       0 lignes

. . import :
"AGRMGXL1"."MGX_ZPE_ZONE_PECHE_DOUCE_POLGN"      0 KB     
0 lignes

Traitement du type d'objet
SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Traitement du type d'objet
SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Traitement du type d'objet SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX

ORA-31684: Le type d'objet
INDEX:"AGRMGXL1"."A106_IX1" existe déjà

ORA-31684: Le type d'objet
INDEX:"AGRMGXL1"."A959_IX1" existe déjà

ORA-39083: Echec de la création du type d'objet INDEX
avec erreur :

ORA-29855: erreur d'exécution de la routine
ODCIINDEXCREATE

ORA-20085: Parameter ST_SRID 3 is different from
ST_GEOMETRY_COLUMNS srid (5).

SQL en échec :

BEGIN

  1. SDE.st_type_export.validate_spref('AGRMGXD1','NAD_1983_Quebec_Lambert',3,-997000,62500,1000,0,1,0,1,999900000000000000000000000000000000,-999900000000000000000000000000000000,999900000000000000000000000000000000,-999900000000000000000000000000000000,999900000000000000000000000000000

ORA-31684: Le type d'objet
INDEX:"AGRMGXL1"."A1120_IX1" existe déjà

ORA-31684: Le type d'objet
INDEX:"AGRMGXL1"."A956_IX1" existe déjà

ORA-31684: Le type d'objet
INDEX:"AGRMGXL1"."A957_IX1" existe déjà

ORA-31684: Le type d'objet
INDEX:"AGRMGXL1"."A121_IX1" existe déjà

ORA-39083: Echec de la création du type d'objet INDEX avec
erreur :

ORA-29855: erreur d'exécution de la routine
ODCIINDEXCREATE

ORA-20085: Parameter ST_SRID 3 is different from
ST_GEOMETRY_COLUMNS srid (6).

SQL en échec :

BEGIN

SDE.st_type_export.validate_spref('AGRMGXD1','NAD_1983_Quebec_Lambert',3,-997000,62500,1000,0,1,0,1,999900000000000000000000000000000000,-999900000000000000000000000000000000,999900000000000000000000000000000000,-99

0 Kudos
YvesThibault
New Contributor II

Hi Christian,

I'm in the process to try the last scenario.  I mean, the one where I create in SIGMAI instance, a user AGRMGXD1 and I will import schémas SDE and AGRMGXD1 (without remap_Schema flag).  If it works, I will simply use ArcCatalog and make copy/paste of all table (feature class) from AGRMGXD1 to AGRMGXL1.  Maybe I will try to do it with a python script (but I don't have a lot a available free space on SIGMAI).

Yves

0 Kudos