My Oracle Support Banner

IMPDP Reports ORA-2298 ORA-39083 While Importing Foreign Keys Which Are Using ON DELETE CASCADE ENABLE (Doc ID 944542.1)

Last updated on APRIL 03, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 02-Nov-2016***

Symptoms

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or built-in schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

 

 

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 <PASSWORD> default tablespace testcase;
grant resource, connect to u1;

create user u2 identified by <PASSWORD> 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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.