My Oracle Support Banner

DataPump Import (IMPDP) Error ORA-31684 Object type REF_CONSTRAINT:"TEST"."B_TAB_FK2" already exists (Doc ID 1681030.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.1 [Release 10.1 to 12.1]
Information in this document applies to any platform.

Symptoms

You started a DataPump import (either using a dump file or a network link) and received the error:

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-31684: Object type REF_CONSTRAINT:"TEST"."B_TAB_FK2" already exists
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

when the referential constraints are imported.

The following test demonstrates this:

-- Source database

connect / as sysdba

create or replace directory tmp as '/tmp';

create user test identified by test default tablespace users temporary tablespace temp;
grant connect, resource to test;

connect test/test

create table a_tab
(
   id number,
   text varchar2(10),
   constraint a_tab_pk primary key (id)
);

create table b_tab
(
   id number,
   text varchar2(10),
   constraint b_tab_fk1 foreign key (id) references a_tab (id),
   constraint b_tab_fk2 foreign key (id) references a_tab (id)
);

-- Export the user TEST
host expdp system/password directory=tmp schemas=test dumpfile=test.dmp

Export: Release 12.1.0.1.0 - Production on Tue Jun 10 15:00:29 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=tmp schemas=test dumpfile=test.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "TEST"."A_TAB"                                  0 KB       0 rows
. . exported "TEST"."B_TAB"                                  0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /tmp/test.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jun 10 15:01:52 2014 elapsed 0 00:01:11

-- Target database
-- Import the user TEST from previous dump
host impdp system/password directory=tmp full=y dumpfile=test.dmp

Import: Release 12.1.0.1.0 - Production on Tue Jun 10 15:02:24 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=tmp full=y dumpfile=test1.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
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 "TEST"."A_TAB"                                  0 KB       0 rows
. . imported "TEST"."B_TAB"                                  0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-31684: Object type REF_CONSTRAINT:"TEST"."B_TAB_FK2" already exists
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Tue Jun 10 15:03:00 2014 elapsed 0 00:00:31

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
References

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