DataPump Import Fails With Errors ORA-31693 ORA-2291 (Doc ID 1062564.1)

Last updated on DECEMBER 17, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 17-Jul-2015***

Symptoms

Errors like the following are reported in the DataPump import log:

ORA-31693: Table data object "JOO"."EMP" failed to load/unload and is being skipped due to error:
ORA-2291: integrity constraint (JOO.FK_DEPTNO) violated - parent key not found


The issue can be reproduced with the following test case:

-- create tables (schema FOO)

CREATE TABLE fDEPT
(
   DEPTNO    NUMBER(2)    CONSTRAINT PK_DEPT PRIMARY KEY,
   DNAME     VARCHAR2(14),
   LOC       VARCHAR2(13)
);

CREATE TABLE EMP
(
   EMPNO     NUMBER(4)    CONSTRAINT PK_EMP PRIMARY KEY,
   ENAME     VARCHAR2(10),
   JOB       VARCHAR2(9),
   MGR       NUMBER(4),
   HIREDATE  DATE,
   SAL       NUMBER(7,2),
   COMM      NUMBER(7,2),
   DEPTNO    NUMBER(2)    CONSTRAINT FK_DEPTNO REFERENCES fDEPT
);

-- run the import

#> impdp dumpfile=const.dmp logfile=constimp.log REMAP_SCHEMA=FOO:JOO TABLE_EXISTS_ACTION= APPEND


You may receive errors like:

ORA-31693: Table data object "JOO"."EMP" failed to load/unload and is being skipped due to error:
ORA-2291: integrity constraint (JOO.FK_DEPTNO) violated - parent key not found
. . imported "JOO"."FDEPT"          5.656 KB   4   rows imported

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms