Oracle Database Discussions

Hi,

I am trying to import a dump using impdp tool with table_exists_action=append mode. However import fails with below error.

ORA-31693: Table data object "TESTDP"."TEST" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (TESTDP.SYS_C0033971) violated

I understad that the above errors were due to existing data,Bbut isn't it supposed to insert the records those are not present in the table.

Here is a simple test I have done to better understand the issue:

CREATE TABLE testdp.TEST( t NUMBER(5) PRIMARY KEY);

INSERT INTO testdp.TEST VALUES ( 1);
INSERT INTO testdp.TEST VALUES ( 2);
INSERT INTO testdp.TEST VALUES ( 3);
INSERT INTO testdp.TEST VALUES ( 4);
INSERT INTO testdp.TEST VALUES ( 5);
COMMIT;

Export the table using expdp:

$expdp testdp/testdp@dbnamel directory=db_dumps dumpfile=test1.dmp logfile=test1.log

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 16 March, 2009 13:23:38
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TESTDP"."SYS_EXPORT_SCHEMA_01": testdp/********@dbname directory=dp_dumps dumpfile=test1.dmp logfile=test1.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "TESTDP"."TEST" 4.992 KB 5 rows
Master table "TESTDP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Note: I have removed few lines to make this notes small.


Now delete few records from table:

DELETE FROM testdp.TEST WHERE t <=3;
3 rows deleted.

COMMIT;
Commit complete.

now import the above exported dump to same account.

$impdp testdp/testdp@dbname directory=dp_dumps dumpfile=test1.dmp logfile=test1_imp.log table_exists_action=append

Import: Release 10.2.0.4.0 - 64bit Production on Monday, 16 March, 2009 13:27:34
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TESTDP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TESTDP"."SYS_IMPORT_FULL_01": testdp/********@dbname directory=dp_dumps dumpfile=test1.dmp logfile=test1_imp.log table_exists_action=append
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39152: Table "TESTDP"."TEST" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TESTDP"."TEST" failed to load/unload and is being skipped due to error:
ORA-00001: unique constraint (TESTDP.SYS_C0033996) violated
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "TESTDP"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 13:27:37


Now select the records from the table;

SELECT * FROM testdp.TEST;
T
----------
4
5

2 rows selected.

Isn't it supposed to insert records ( 1, 2, 3). it works fine with regular exp/imp utility with ignore=y option. but doesn't work with datapump with table_exists_action=append.

is this a bug? did any one of you have this issue? I have tested it on SUSE LINUX Enterprise Server 9 (x86_64) and HP-UX B.11.23

Thanks,
Krishna.