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 laterOracle 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;
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.
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 |