Datapump Import error–While Importing dump file from One Database to another Database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Datapump Import error–While Importing dump file from One Database to another Database

  1. #1
    Join Date
    Apr 2006
    Posts
    23

    Datapump Import error–While Importing dump file from One Database to another Database

    Hi ALL GURUS
    I am using datapump utility (10g R2) to import database from Database A to Database B .While importing datapump export file I am getting following error. Is any body has any idea to avoid these error. One option is to create a same Tablespace as source Database A in target database i.e Database B .
    Import syntax.
    C:> IMPDP system/********@ora1 SCHEMAS=COL1 REMAP_SCHEMA=COL1:COL2 DIRECTORY=DBPUMP_DIR1 DUMPFILE=DUMP_ONLY_SCHEMA_COL1.DMP LOGFILE=DUMP_ONLY_SCHEMA_COL1.log
    Note umpfile DUMP_ONLY_SCHEMA_COL1.DMP has been manually copied to Directory (DBPUMP_DIR1) on target database B.
    Error:
    Failing sql is:
    CREATE TABLE "COL2."."ABC" ("ACCT" VARCHAR2(15) NOT NULL ENABLE, "BUS" VARCHAR2(5) NOT NULL ENABLE, "RECORD" VARCHAR2(2), "ACCO" VARCHAR2(40), "ACCTC” VARCHAR2(40), "CO
    ORA-39083: Object type TABLE failed to create with error:
    ORA-00959: tablespace 'MA_TEST' does not exist
    However when we use original exp/imp we need not to create tablespaces just do fromuser touser is fine.
    Any clue to avoid above error in expdp/impdp.

    Regards

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    there is an option in datapump to remap the tablespaces, cant remmeber the exact parameter off the top of my head though, look atr the docs or impdb help=y for a clue

  3. #3
    Join Date
    Apr 2006
    Posts
    23
    Thanks for your tips there is a option in Datapump to remap the tablespace i.e.REMAP_TABLESPACE. Therefore when we are do User to User and database to database then one has to define REMAP_SCHEMA as well as REMAP_TABLESPACE.
    e.g.
    C:> IMPDP system/********@ora1 SCHEMAS=COL1 REMAP_SCHEMA=COL1:COL2 REMAP_TABLESPACE=LABTEST1:LABTEST2 DIRECTORY=DBPUMP_DIR1 DUMPFILE=DUMP_ONLY_SCHEMA_COL1.DMP LOGFILE=DUMP_ONLY_SCHEMA_COL1.log

Posting Permissions

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



Click Here to Expand Forum to Full Width