Importer un dumpfile avec un datapump import

**meka jores** · 27/06/2014, 20h33

bonjour je voudrais importer un dumpfile dans un user
voila le script que j'ai fait:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 impdp joresenergizer/cnps directory=jorespump dumpfile=jores11.dmp remap_schema=energizer:joresenerizer

ci-dessous vous avez l'erreur de mon interface que j'ai copié

Microsoft Windows [version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. Tous droits réservés.

C:\Users\stagiaires> impdp joresenergizer/cnps directory=jorespump dumpfile=jores11.dmp remap_schema=energizer:joresenerizer

Import: Release 11.2.0.2.0 - Beta on Ven. Juin 27 18:05:54 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta
Master table "JORESENERGIZER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JORESENERGIZER"."SYS_IMPORT_FULL_01": joresenergizer/******** directory=jorespump dumpfile=jores11.dmp remap_schema=energizer:joresene
rizer
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"JORESENERIZER"."JASSURE" failed to create with error:
ORA-01918: user 'JORESENERIZER' does not exist
Failing sql is:
CREATE TABLE "JORESENERIZER"."JASSURE" ("NUM_ASSU" VARCHAR2(13 CHAR) NOT NULL ENABLE, "NOM_ASSU" VARCHAR2(60 CHAR), "PRENOM_ASSU" VARCHAR2(60 CHA
R), "DATE_NAISS_ASSU" DATE, "NOM_JEUNE_FILLE_ASSU" VARCHAR2(25 CHAR), "SEXE_ASSU" VARCHAR2(1 CHAR), "CIVILITE_ASSU" VARCHAR2(5 CHAR), "TEL_ASSU"
VARCHAR2(20 CHAR), "FAX_ASSU" VARCHAR2(20 CHAR), "EMAIL_ASSU" VARCHAR2(50
ORA-39083: Object type TABLE:"JORESENERIZER"."TCOMPTE_ASSURE" failed to create with error:
ORA-01918: user 'JORESENERIZER' does not exist
Failing sql is:
CREATE TABLE "JORESENERIZER"."TCOMPTE_ASSURE" ("NUMERO_DIPE" NUMBER(5,0) NOT NULL ENABLE, "NUMERO_CONTRIB" VARCHAR2(20 CHAR) DEFAULT NULL, "MOIS_
DIPE" NUMBER(2,0) NOT NULL ENABLE, "MATRICULE_EMPL" VARCHAR2(17 CHAR) NOT NULL ENABLE, "REGIME" NUMBER(1,0) NOT NULL ENABLE, "ANNEE" NUMBER(4,0)
NOT NULL ENABLE, "MATRICULE_ASS" VARCHAR2(13 BYTE) DEFAULT NULL, "
ORA-39083: Object type TABLE:"JORESENERIZER"."JASSURE_DELETE2" failed to create with error:
ORA-01918: user 'JORESENERIZER' does not exist
Failing sql is:
CREATE TABLE "JORESENERIZER"."JASSURE_DELETE2" ("NUM_ASSU" VARCHAR2(13 CHAR) NOT NULL ENABLE, "NOM_ASSU" VARCHAR2(60 CHAR), "PRENOM_ASSU" VARCHAR
2(60 CHAR), "DATE_NAISS_ASSU" DATE, "NOM_JEUNE_FILLE_ASSU" VARCHAR2(25 CHAR), "SEXE_ASSU" VARCHAR2(1 CHAR), "CIVILITE_ASSU" VARCHAR2(5 CHAR), "TE
L_ASSU" VARCHAR2(20 CHAR), "FAX_ASSU" VARCHAR2(20 CHAR), "EMAIL_A
ORA-39083: Object type TABLE:"JORESENERIZER"."JEMPLOYEUR" failed to create with error:
ORA-01918: user 'JORESENERIZER' does not exist
Failing sql is:
CREATE TABLE "JORESENERIZER"."JEMPLOYEUR" ("NUM_EMPLOYEUR" VARCHAR2(17 CHAR) NOT NULL ENABLE, "RAISON_SOCIALE" VARCHAR2(200 CHAR), "SIGLE" VARCHA
R2(30 CHAR), "ADRESSE_EMPLOYEUR" VARCHAR2(100 CHAR), "BOITE_POSTALE" VARCHAR2(10 CHAR), "TEL" VARCHAR2(30 CHAR), "FAX" VARCHAR2(20 CHAR), "EMAIL"
VARCHAR2(50 CHAR), "CODE_ARROND" VARCHAR2(6 CHAR), "CODE_PAYS" VARCHA
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "ERIC_NGAH"."CIRCUIT_DOSSIEREM_FK_IDX" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt
varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',
6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "ERIC_NGAH"."JEMPLOYEUR_LIEUNAISS_IDX" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt
varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',
6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "ERIC_NGAH"."FK_CODENA_JEMPLOYEUR_IDX" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt
varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',
6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "JORESENERGIZER"."SYS_IMPORT_FULL_01" completed with 7 error(s) at 18:05:58

C:\Users\stagiaires>

Bien vouloir m'aider SVP

MERCI

**Etanne** · 27/06/2014, 21h18

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

C:\Users\stagiaires>

Aaaah les stagiaires et la flemme de chercher !

Bon, plus sérieusement, je vous conseil de bien lire ce que Oracle a inscrit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta
Master table "JORESENERGIZER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JORESENERGIZER"."SYS_IMPORT_FULL_01": joresenergizer/******** directory=jorespump dumpfile=jores11.dmp remap_schema=energizer:joresene
rizer
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"JORESENERIZER"."JASSURE" failed to create with error:
ORA-01918: user 'JORESENERIZER' does not exist
Failing sql is:
CREATE TABLE "JORESENERIZER"."JASSURE" ("NUM_ASSU" VARCHAR2(13 CHAR) NOT NULL ENABLE, "NOM_ASSU" VARCHAR2(60 CHAR), "PRENOM_ASSU" VARCHAR2(

L'utilisateur JORESENERIZER n'existe pas

et il faut donc le créer !

Etanne

**meka jores** · 28/06/2014, 14h54

Excusez moi c'est une erreur.
je l'ai corrigé
malgré cela j'ai toujours des erreurs
voilà ci-dessous ce que me renvoie toujours mon interface

Microsoft Windows [version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. Tous droits réservés.

C:\Users\stagiaires>impdp joresenergizer/cnps directory=jorespump dumpfile=jores11.dmp remap_schema=energizer:joresenergizer

Import: Release 11.2.0.2.0 - Beta on Sam. Juin 28 12:46:07 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta
Master table "JORESENERGIZER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JORESENERGIZER"."SYS_IMPORT_FULL_01": joresenergizer/******** directory=jorespump dumpfile=jores11.dmp remap_schema=energizer:joresene
rgizer
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"JORESENERGIZER"."JASSURE" failed to create with error:
ORA-00959: tablespace 'CNPS_ENERGIZER_TB' does not exist
Failing sql is:
CREATE TABLE "JORESENERGIZER"."JASSURE" ("NUM_ASSU" VARCHAR2(13 CHAR) NOT NULL ENABLE, "NOM_ASSU" VARCHAR2(60 CHAR), "PRENOM_ASSU" VARCHAR2(60 CH
AR), "DATE_NAISS_ASSU" DATE, "NOM_JEUNE_FILLE_ASSU" VARCHAR2(25 CHAR), "SEXE_ASSU" VARCHAR2(1 CHAR), "CIVILITE_ASSU" VARCHAR2(5 CHAR), "TEL_ASSU"
VARCHAR2(20 CHAR), "FAX_ASSU" VARCHAR2(20 CHAR), "EMAIL_ASSU"
ORA-39083: Object type TABLE:"JORESENERGIZER"."TCOMPTE_ASSURE" failed to create with error:
ORA-00959: tablespace 'CNPS_ENERGIZER_TB' does not exist
Failing sql is:
CREATE TABLE "JORESENERGIZER"."TCOMPTE_ASSURE" ("NUMERO_DIPE" NUMBER(5,0) NOT NULL ENABLE, "NUMERO_CONTRIB" VARCHAR2(20 CHAR) DEFAULT NULL, "MOIS
_DIPE" NUMBER(2,0) NOT NULL ENABLE, "MATRICULE_EMPL" VARCHAR2(17 CHAR) NOT NULL ENABLE, "REGIME" NUMBER(1,0) NOT NULL ENABLE, "ANNEE" NUMBER(4,0)
NOT NULL ENABLE, "MATRICULE_ASS" VARCHAR2(13 BYTE) DEF
ORA-39083: Object type TABLE:"JORESENERGIZER"."JASSURE_DELETE2" failed to create with error:
ORA-00959: tablespace 'CNPS_ENERGIZER_TB' does not exist
Failing sql is:
CREATE TABLE "JORESENERGIZER"."JASSURE_DELETE2" ("NUM_ASSU" VARCHAR2(13 CHAR) NOT NULL ENABLE, "NOM_ASSU" VARCHAR2(60 CHAR), "PRENOM_ASSU" VARCHA
R2(60 CHAR), "DATE_NAISS_ASSU" DATE, "NOM_JEUNE_FILLE_ASSU" VARCHAR2(25 CHAR), "SEXE_ASSU" VARCHAR2(1 CHAR), "CIVILITE_ASSU" VARCHAR2(5 CHAR), "T
EL_ASSU" VARCHAR2(20 CHAR), "FAX_ASSU" VARCHAR2(20 CHA
ORA-39083: Object type TABLE:"JORESENERGIZER"."JEMPLOYEUR" failed to create with error:
ORA-00959: tablespace 'CNPS_ENERGIZER_TB' does not exist
Failing sql is:
CREATE TABLE "JORESENERGIZER"."JEMPLOYEUR" ("NUM_EMPLOYEUR" VARCHAR2(17 CHAR) NOT NULL ENABLE, "RAISON_SOCIALE" VARCHAR2(200 CHAR), "SIGLE" VARCH
AR2(30 CHAR), "ADRESSE_EMPLOYEUR" VARCHAR2(100 CHAR), "BOITE_POSTALE" VARCHAR2(10 CHAR), "TEL" VARCHAR2(30 CHAR), "FAX" VARCHAR2(20 CHAR), "EMAIL
" VARCHAR2(50 CHAR), "CODE_ARROND" VARCHAR2(6 CHAR), "CODE_
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "ERIC_NGAH"."CIRCUIT_DOSSIEREM_FK_IDX" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt
varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',
6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "ERIC_NGAH"."JEMPLOYEUR_LIEUNAISS_IDX" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt
varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',
6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,
ORA-39083: Object type INDEX_STATISTICS failed to create with error:
ORA-20000: INDEX "ERIC_NGAH"."FK_CODENA_JEMPLOYEUR_IDX" does not exist or insufficient privileges
Failing sql is:
DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt
varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',
6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "JORESENERGIZER"."SYS_IMPORT_FULL_01" completed with 7 error(s) at 12:46:18

C:\Users\stagiaires>

je suis toujours coincé
Aidez moi SVP
merci

**Etanne** · 28/06/2014, 16h35

Avez-vous lu les messages ?!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta
Master table "JORESENERGIZER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JORESENERGIZER"."SYS_IMPORT_FULL_01": joresenergizer/******** directory=jorespump dumpfile=jores11.dmp remap_schema=energizer:joresene
rgizer
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"JORESENERGIZER"."JASSURE" failed to create with error:
ORA-00959: tablespace 'CNPS_ENERGIZER_TB' does not exist
Failing sql is:
CREATE TABLE "JORESENERGIZER"."JASSURE" ("NUM_ASSU" VARCHAR2(13 CHAR) NOT NULL ENABLE, "NOM_ASSU" VARCHAR2(60 CHAR), "PRENOM_ASSU" VARCHAR2(60 CH
AR), "DATE_NAISS_ASSU" DATE, "NOM_JEUNE_FILLE_ASSU" VARCHAR2(25 CHAR), "SEXE_ASSU" VARCHAR2(1 CHAR), "CIVILITE_ASSU" VARCHAR2(5 CHAR), "TEL_ASSU"
VARCHAR2(20 CHAR), "FAX_ASSU" VARCHAR2(20 CHAR), "EMAIL_ASSU"

**meka jores** · 30/06/2014, 11h14

justement c'est là que je suis coincé!

En faite, le tablespaces(CNPS_ENERGIZER_TB) est celui de la BD d'où vient le dumpfile.

Faut_il que je le créé aussi dans ma BD destinataire 'joresenergizer'?
où alors ya t_il des options qu'il faut ajouter à mon script?
si oui,lesquels car moi je n'ai pas d’idée!!!!
Merci pour votre compréhension

**Heaven93** · 01/07/2014, 01h24

bonsoir,

pour importer des objets qui n'ont pas été exportés par soi, qui appartiennent à un autre schéma il faut avoir le privilège imp_full_database.
grantez le à joresenergizer. sinon le user system est une bonne piste pour l'import
et les imports d'export fulls sont à réfléchir : pourquoi recréer un tablespace? créer le user cible avec un tablespace par défaut où les objets seront stockés parait plus simple. il faut éviter la facilité du full autant que possible

Importer un dumpfile avec un datapump import

Import/Export Oracle

Vue hybride

Discussions similaires

Partager

Partager