My Oracle Support Banner

11gR2 is Missing Constraints After DataPump Export/Import (Doc ID 1310801.1)

Last updated on FEBRUARY 14, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 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 Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data .Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Symptoms

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

connect test/<password>

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.

Changes

 NONE

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
Changes
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.