Hi All, I like to share my experience with recent database migration to Exadata Machine using Oracle Export / Import utility. Many of us used Oracle Export / Import utility for database upgrades and migrations. If you are migrating data using Data Pump with all the constraints and indexes, you will not face this issue. This issue is only related to Data Pump option Exclude / Include = Constraints.
Let me start with the reason that why would we need to copy data using data pump without constraints or indexes. When you are migration few GB of data it will not matter that you are creating and build indexes during data import. But if you are planning to migrate 10+ TB database using data pump, you want to separate data copy from indexes and constraints. Creating indexes and constraints on terabyte tables can take days since they will run without any parallelism. Hence many DBA’s and migration experts uses Data Pump SQLFILE option for indexes and constraints creation. This way we are able to create indexes in parallel mode and create constraints without validate.
Now with 12c Oracle has change exclude option and it will not import NOT NULL constraints if you exclude constraints during data import. Please review following Oracle Support note and make sure to bring NOT NULL constraints manually after the data import. Fortunately, data import with exclude constraints option still bring CHECK constraints for now.
Data Pump Import With EXCLUDE=CONSTRAINT Or INCLUDE=CONSTRAINT Is Excluding And Respectively Not Including NOT NULL Constraints (Doc ID 1930631.1)
After
doing a Data Pump import (impdp) in 12.1.0.2 database release with parameter
EXCLUDE=CONSTRAINT the NOT NULL constraints from source tables are not in
target tables. Sequence of steps leading to the problem:
– Data Pump export of tables with NOT NULL and referential integrity
constraints
– Data Pump import with EXCLUDE=CONSTRAINT
For example:
==> In source database a table has the following structure:
Name Null? Type
—————————————– ——– —————————-
ID NOT NULL NUMBER(38)
NAME NOT NULL VARCHAR2(20)
AGE NOT NULL NUMBER(38)
ADDRESS CHAR(25)
SALARY NUMBER(18,2)
=> After import, the table structure:
Name Null? Type
—————————————–
——– —————————-
ID NUMBER(38) ———————> NOT NULL
Constraints ignored
NAME VARCHAR2(20)———————> NOT
NULL Constraints ignored
AGE NUMBER(38)———————> NOT NULL
Constraints ignored
ADDRESS CHAR(25)
SALARY NUMBER(18,2)
Also, if using Data Pump import (impdp) with INCLUDE=CONSTRAINT parameter, NOT NULL constraints are not imported.