Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Importing partial dump over full dump

samiJul 9 2014 — edited Jul 18 2014

Dear All,

Am using

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

I have requirement that Due to huge size and time taking more than 8 hours to take dump using expdp.

So I plan to take full on week end and partial dump that is (few tables have been partition into 3 parts which has more 2 GB  such part1,part2,part3. Part3 always have latest data(maxvalue) .

I Used to backup of all tables(non partition tables) with part3 partition table data's.

Above scenario working fine.

Facing problem in the following scenario is

Now  I needs to import the full dump on week end and import partial dump on week days on DR.

Full dump is working fine.

While importing partial dump I needs import latest partial data's with all tables(non partition ) without distributing the existing data's(Part1, Part2 partition data)

Partial Import with append option

Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** REMAP_SCHEMA=scott:scott1 directory=DUMPDIR dumpfile=SCOTT_PART_09-07-14.DMP logfile=imp_scott_09-07-14.log TRANSFORM=SEGMENT_ATTRIBUTES:n TABLE_EXISTS_ACTION=append EXCLUDE=CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39152: Table "SCOTT1"."TEMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

ORA-39152: Table "SCOTT1"."DEPT" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

ORA-39152: Table "SCOTT1"."EMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

ORA-39152: Table "SCOTT1"."BONUS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

ORA-39152: Table "SCOTT1"."SALGRADE" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

ORA-31693: Table data object "SCOTT1"."TEMP":"TEMP_PART2" failed to load/unload and is being skipped due to error:

ORA-00001: unique constraint (SCOTT1.TEMP_BAK_PK) violated

ORA-31693: Table data object "SCOTT1"."DEPT" failed to load/unload and is being skipped due to error:

ORA-00001: unique constraint (SCOTT1.PK_DEPT) violated

ORA-31693: Table data object "SCOTT1"."EMP" failed to load/unload and is being skipped due to error:

ORA-00001: unique constraint (SCOTT1.PK_EMP) violated

. . imported "SCOTT1"."SALGRADE"                         5.585 KB       5 rows

. . imported "SCOTT1"."BONUS"                                0 KB       0 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 8 error(s) at 15:12:59

APPEND option doesn't working or not appending the new records to table. Its skipped due to above error. since I have used EXCLUDE=CONSTRAINT option as well.

I have tried  REPLACE option which deletes the existing partition data(part1, Part2) but my requirement is  Part1, Part2 should not be removed from table.

Kindly guide me to resolve the above error.

Thanks & Regards

Sami.

This post has been answered by Richard Harrison . on Jul 18 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 15 2014
Added on Jul 9 2014
10 comments
4,324 views