11gR2 is Missing Constraints After DataPump Export/Import

(Doc ID 1310801.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

You created the following table in an Oracle database version < 11.2 (line 10gR1, 10gR2 or 11gR1):

connect test/test

create table atable (acol number not null, bcol number not null);
alter table atable add supplemental log data (primary key) columns;
alter table atable add supplemental log data (foreign key) columns;
alter table atable add supplemental log data (unique index) columns;

The created constraints are the following:

connect / as sysdba
select owner, table_name, constraint_name, constraint_type, search_condition
from   dba_constraints
where  owner = 'TEST' and
       table_name = 'ATABLE';

-- 10g
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION
----- ---------- --------------- - ------------------
TEST  ATABLE     SYS_C0045910    C "BCOL" IS NOT NULL
TEST  ATABLE     SYS_C0045909    C "ACOL" IS NOT NULL
TEST  ATABLE     SYS_C0045914    ?
TEST  ATABLE     SYS_C0045913    ?
TEST  ATABLE     SYS_C0045912    ?

--11gR1
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION
----- ---------- --------------- - ------------------
TEST  ATABLE     SYS_C0023616    C "BCOL" IS NOT NULL
TEST  ATABLE     SYS_C0023615    C "ACOL" IS NOT NULL
TEST  ATABLE     SYS_C0023619    S
TEST  ATABLE     SYS_C0023618    S
TEST  ATABLE     SYS_C0023617    S

Then you performed a schema export (DataPump) using the command line:

expdp system/password directory=dpu dumpfile=test.dmp schemas=test

and imported the new dump in Oracle database version 11gR2 (11.2.0.1 or 11.2.0.2) with:

impdp system/password directory=dpu dumpfile=test.dmp full=y

You observed that some constraints are missing in target database:

connect / as sysdba
select owner, table_name, constraint_name, constraint_type, search_condition
from   dba_constraints
where  owner = 'TEST' and
       table_name = 'ATABLE';

--11gR2
OWNER TABLE_NAME CONSTRAINT_NAME C SEARCH_CONDITION
----- ---------- --------------- - ------------------
TEST  ATABLE     SYS_C0014333    C "BCOL" IS NOT NULL
TEST  ATABLE     SYS_C0014332    C "ACOL" IS NOT NULL

The constraints of type "?" (10g) or "S" (11gR1) are not available.

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