Data Dump Import Missing NOT NULL Constraints

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.