Oracle Database Discussions

Hi,

Oracle Version:10.2.0.1

Here i have small doubt in impdp with the parameter table_exists_action=append.

I had taken the datapump export from the schema A and importing the dump in to the schema B in the same database.Here I took the backup the table DEPT from A and Importing it table DEPT in B Schema .In A schema the table DEPT is having the 6 rows and in the schema B table dept is having 5 rows.

Here is the output of my table dept in schema A

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        70 APD            VTL-3
        50 DBA            HYDERABAD
        10 ACCOUNTING     NEW YORK
        20 ADMIN          DALLAS
        30 TESTING        HYDERABAD
        40 OPERATIONS     BOSTON

6 rows selected.

Here is the output of my table dept in schema B

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 DBA            HYDERABAD
        10 ACCOUNTING     NEW YORK
        20 ADMIN          DALLAS
        30 TESTING        HYDERABAD
        40 OPERATIONS     BOSTON

And here is the output on the import process

E:\oracle\dbdump>impdp scott/tiger directory=dbdump dumpfile=POORNA.DMP logfile=scott_12.log remap_schema=poorna:scott tables=dept table_exists_action=append

Import: Release 10.2.0.1.0 - Production on Friday, 20 November, 2009 10:29:55

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=dbdump dumpfile=POORNA.DMP logfile=scott_12.log remap_schema=poorna:scott tables=dept table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "SCOTT"."DEPT" 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 "SCOTT"."DEPT" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (SCOTT.DEPT_PRIMARY_KEY) violated
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at 10:30:03

Here my doubt is if we use the Table_Exists_Action=Append means it will the append the new rows which is not there in the table.But why the import process failed with out importing the single row with the value deptno =70


Please help me.