This discussion is archived
5 Replies Latest reply: Jan 8, 2013 2:33 PM by 736874 RSS

Partial IMPDP Ora 11g Windows

736874 Newbie
Currently Being Moderated
I ran an IMPDP from a full export file. I did not have two of the tablespaces setup that the import needed.
Question:
If I setup the missing tblsp's can I just rerun the full install without damaging the tables already loaded from my first run?
If not can I can a partial impdp and only import teh two tablespaces I'm missing?
I have read through and I clearly dont know enough about Oracle to figure out what will happen if I try to rerun.

Thank you for any input.
Carter
  • 1. Re: Partial IMPDP Ora 11g Windows
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post exact OS and database versions, along with the complete impdp command used and the errors encountered.

    It is possible to rerun the import - you will need to set TABLE_EXISTS_ACTION parameter appropriately

    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#sthref299

    HTH
    Srini
  • 2. Re: Partial IMPDP Ora 11g Windows
    736874 Newbie
    Currently Being Moderated
    Win 2008 r2
    Oracle 11g Standard
    IMPDP SYSTEM/PSWRD dumpfile=pdcad2_9.dmp logfile=pdcad2_9.log full=y;

    Errors

    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    ORA-39083: Object type TABLE:"CAD"."AGENC" failed to create with error:
    ORA-00959: tablespace 'DBM' does not exist
    Failing sql is:
    CREATE TABLE "CAD"."AGENC" ("ACT_PLN" VARCHAR2(30 BYTE) NOT NULL ENABLE, "AG_ADD" VARCHAR2(30 BYTE), "AG_CITY" VARCHAR2(20 BYTE), "AG_ID" VARCHAR2(9 BYTE) NOT NULL ENABLE, "AG_NME" VARCHAR2(50 BYTE), "AG_ST" VARCHAR2(4 BYTE), "AG_TYP" VARCHAR2(8 BYTE), "AG_ZIP" VARCHAR2(10 BYTE), "CONTACT" VARCHAR2(20 BYTE), "LINEUP_NAME" VARCHAR2(30 BYTE), "NUM_1" NUMBER(10,0) NOT NULL
    ORA-39083: Object type TABLE:"CAD"."ALARM" failed to create with error:
    ORA-00959: tablespace 'DBM' does not exist
    ...
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
    Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 769 error(s) at 14:44:45
  • 3. Re: Partial IMPDP Ora 11g Windows
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Thanks - pl re-run the import and it will skip existing tables and create the missing ones

    HTH
    Srini
  • 4. Re: Partial IMPDP Ora 11g Windows
    Dean Gagne Expert
    Currently Being Moderated
    Yes, just rerun the impdp command. By default, Data Pump skips objects that already exist. So, since you already imported some tables, when the second import sees those tables, it will try to create them, but when the create fails, it will skip over them. If you ever want import to replace the tables, then use:

    table_exists_action=replace

    by default, this parameter is set to 'SKIP'.

    Hope this helps.

    Dean
  • 5. Re: Partial IMPDP Ora 11g Windows
    736874 Newbie
    Currently Being Moderated
    Thank you for your replies. I reran my IMPDP and now have a working backup.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points