IMPDP Reports ORA-2298 ORA-39083 While Importing Foreign Keys Which Are Using ON DELETE CASCADE ENABLE

(Doc ID 944542.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 02-Nov-2016***

Symptoms

DataPump import (impdp) reports ORA-2298 and ORA-39083 when importing Foreign Key constraints which were altered to use the the ON DELETE clause.

The test case below will illustrate this:

connect / as sysdba

drop user u1 cascade;
drop user u2 cascade;

create user u1 identified by u1 default tablespace testcase;
grant resource, connect to u1;

create user u2 identified by u2 default tablespace testcase;
grant resource, connect to u2;

-- Create Parent Table
create table u1.parent1 (col1 number, col2 varchar2(10));

alter table u1.parent1 add constraint pk_parent1_col1 primary key(col1);

insert into u1.parent1 values (1, 'row1');
insert into u1.parent1 values (2, 'row2');
insert into u1.parent1 values (3, 'row3');
insert into u1.parent1 values (4, 'row4');
commit;

-- Create Child Table
create table u1.child1 (child1_col1 number, child1_col2 varchar2(30));

alter table u1.child1 add foreign key (child1_col1) references u1.parent1 (col1)
ON DELETE CASCADE ENABLE);

insert into u1.child1 values (1, 'points to parent key: 1');
insert into u1.child1 values (2, 'points to parent key: 2');
insert into u1.child1 values (3, 'points to parent key: 3');
insert into u1.child1 values (4, 'points to parent key: 4');
commit;

-- Export
expdp system/****** DIRECTORY=test_dp DUMPFILE=export_schemas.dmp schemas=u1
                    query=u1.parent1:"where col1=1" 
-- Import
impdp system/****** DIRECTORY=test_dp DUMPFILE=export_schemas.dmp remap_schema=u1:u2  


The import log file shows:

Import: Release 10.2.0.4.0 - Production on Saturday, 26 September, 2009 22:23:03

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** DIRECTORY=test_dp
DUMPFILE=export_schemas.dmp remap_schema=u1:u2
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"U2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U2"."CHILD1"                      5.351 KB       4 rows
. . imported "U2"."PARENT1"                     5.234 KB       1 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (U2.SYS_C0045127) - parent keys not found
Failing sql is:
ALTER TABLE "U2"."CHILD1" ADD FOREIGN KEY ("CHILD1_COL1") REFERENCES
"U2"."PARENT1" ("COL1") ON DELETE CASCADE ENABLE

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