Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Unanswered: IMPDP with constraints

    Hi at all,

    I'm a newbie in Oracle datapump and I've a question.

    I need to migrate a database from a server to another server.

    I used

    expdp system/password@SID directory=EXP_DIR SCHEMAS=schema1,schema2 dumpfile=exp_all_schemas.dmp logfile=exp_all_schemas.log

    to export data from first server.

    Now on the second server I think to use

    impdp system/password@SID directory=IMP_DIR SCHEMAS=schema1,schema2 dumpfile=exp_all_schemas.dmp logfile=imp_all_schemas.log

    but in schema1 I've some tables with foreign keys to other tables of schema2.

    I think that the import will returns some errors because it import firstly schema1 and when it try to create foreign keys, while schema2 is still not imported, it fails.

    How can I solve this problem?

    Many Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    I'm not all that experienced with DataPump so I don't know any sophisticated ways to accomplish this. However, seeing as how it Friday afternoon and no one else seems to be around, I'll offer a brute force method.

    IF you don't have similar dependencies in schema 2 (for instance no FKs in schema2 pointing to schema1) then do the schemas seperately and do schema2 first. If there are dependencies in schema2 on schema1 objects, then I am not sure what to do.

    Hope this helps.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If it works anything like export and import, it builds the tables and does the constraints last.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    I don't think it's the referential integrity constraints, they're not enabled until towards the end of the load. You might be able to better get a handle on the problem if you use this approach:

    1) impdp with "content=metadata_only", and get all of the objects created
    2) manually disable Referential Integrity constraints
    3) impdp against same file with "content=data_only"
    4) manually enable all Referential Integrity constraints

    But just plain old expdp/impdp against something like this:
    Code:
    create user user1 identified by oracle 
    quota unlimited on users;
    
    create user user2 identified by oracle 
    quota unlimited on users;
    
    create table user1.parent (pk number primary key);
    
    grant select on user1.parent to user2;
    grant references on user1.parent to user2;
    
    create table user2.child (fk number references user1.parent (pk));
    
    insert into user1.parent values (1);
    insert into user2.child values (1);
    using

    Code:
    [oracle@ora12 dmp]$ vi expdpdev_user1_2_20081006.par
    schemas=user1,user2
    directory=dpump_dir_dev
    dumpfile=expdpdev_user1_2_20081006.dmp
    logfile=expdpdev_user1_2_20081006.log
    
    [oracle@ora12 dmp]$ impdp forbesc@dev parfile=impdpdev_user1_2_20081006.par 
    
    Import: Release 10.1.0.4.0 - 64bit Production on Monday, 06 October, 2008 8:05
    
    Copyright (c) 2003, Oracle.  All rights reserved.
    Password: 
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    Master table "FORBESC"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
    Starting "FORBESC"."SYS_IMPORT_SCHEMA_02":  forbesc/********@dev parfile=impdpdev_user1_2_20081006.par 
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    . . imported "USER1"."PARENT"                            4.921 KB       1 rows
    . . imported "USER2"."CHILD"                             4.921 KB       1 rows
    Processing object type SCHEMA_EXPORT/TABLE/GRANT/OBJECT_GRANT
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Job "FORBESC"."SYS_IMPORT_SCHEMA_02" successfully completed at 08:05
    Code:
    drop user user2 cascade;
    drop user user1 cascade;
    Code:
    [oracle@ora12 dmp]$ vi impdpdev_user1_2_20081006.par
    schemas=user1,user2
    directory=dpump_dir_dev
    dumpfile=expdpdev_user1_2_20081006.dmp
    logfile=impdpdev_user1_2_20081006.log
    
    [oracle@ora12 dmp]$ impdp forbesc@dev parfile=impdpdev_user1_2_20081006.par 
    
    Import: Release 10.1.0.4.0 - 64bit Production on Monday, 06 October, 2008 8:05
    
    Copyright (c) 2003, Oracle.  All rights reserved.
    Password: 
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    Master table "FORBESC"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
    Starting "FORBESC"."SYS_IMPORT_SCHEMA_02":  forbesc/********@dev parfile=impdpdev_user1_2_20081006.par 
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    . . imported "USER1"."PARENT"                            4.921 KB       1 rows
    . . imported "USER2"."CHILD"                             4.921 KB       1 rows
    Processing object type SCHEMA_EXPORT/TABLE/GRANT/OBJECT_GRANT
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
    Job "FORBESC"."SYS_IMPORT_SCHEMA_02" successfully completed at 08:05
    You can see that at the very end of that second logfile the line "Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT", which indicates that the foreign keys aren't created until the very end.

    --=Chuck

  5. #5
    Join Date
    Oct 2008
    Posts
    4
    take your time and learn it from scratch...dont just jump around....it will mess you up.....gl

  6. #6
    Join Date
    Oct 2008
    Posts
    2
    Thanks to all,

    I used

    impdp system/password@SID directory=IMP_DIR SCHEMAS=schema1,schema2 dumpfile=exp_all_schemas.dmp logfile=imp_all_schemas.log

    and it has worked fine because Oracle apply constraints after tables creation.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •