11gR2 is Missing Constraints After DataPump Export/Import
Last updated on NOVEMBER 28, 2016
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and laterInformation 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;
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
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
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