How to Speed up Data Pump Imports Using NOVALIDATE Constraints

Daniel Overby Hansen Data Pump October 3, 2024September 23, 2025 5 Minutes

If you want to save time during a Data Pump import, you can transform constraints to NOT VALIDATED. Regardless of the constraint state in the source database, Data Pump will create the constraint using the novalidate keyword.

This can dramatically reduce the time it takes to import. But be aware of the drawbacks.

The Problem

Here is an example from a big import:

01-JAN-24 08:19:00.257: W-38 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
01-JAN-24 18:39:54.225: W-122      Completed 767 CONSTRAINT objects in 37253 seconds
01-JAN-24 18:39:54.225: W-122      Completed by worker 1 767 CONSTRAINT objects in 37253 seconds

There is only one worker processing constraints, and it took more than 10 hours to add 767 constraints. Ouch!

A Word About Constraints

Luckily, most databases use constraints extensively to enforce data quality. A constraint can be:

VALIDATED
- All data in the table obeys the constraint.
- The database guarantees that data is good.
NOT VALIDATED
- All data in the table may or may not obey the constraint.
- The database does not know if the data is good.

When you create a new constraint using the VALIDATE keyword (which is also the default), the database recursively full scans the entire table to ensure existing data is good. If you add more constraints, the database full scans each time. Since full table scans rarely make it into the buffer cache, each new constraint causes a lot of physical reads.

How Does Data Pump Add Constraints

Data Pump adds the constraints in the same state as in the source. As mentioned above, the constraints are most likely VALIDATED.

During import, Data Pump:

Creates an empty table
Loads data
Adds dependent objects, like constraints

It could look like this in a simplified manner:

For each of the alter table ... add constraint commands will trigger a full table scan because of the validate keyword. For a large database, this really hurts, especially because the full scan does not go parallel.

The Solution

The idea is to add the constraints as NOT VALIDATED but still ENABLED.

NOT VALIDATED means the database doesn’t check the existing data
ENABLED means the database enforces the constraints for new data

In Data Pump, there is a simple transformation:

impdp ... transform=constraint_novalidate:y

Data Pump adds all constraints using the novalidate keyword regardless of the state in the source database.

Instead of a full table scan for each new constraint, the alter table ... add constraint command is instant. It’s just a short write to the data dictionary, and that’s it. No full table scan.

This transformation requires Oracle Database 19c, Release Update 23 with the Data Pump Bundle Patch.

Update: A few ran into the following error when using the feature:

ORA-39001: invalid argument value
ORA-39042: invalid transform name CONSTRAINT_NOVALIDATE

Unfortunately, you need to add patch 37280692 as well. It’s included in 19.27.0 Data Pump Bundle Patch.

Is It Safe To Use?

Yes. There is no chance that this feature corrupts your data. Further, you know that data was good in the source, so it will be good in the target database as well.

However, you should take care when you are changing data on import. The alteration might lead to constraints being unable to validate and you won’t know this until you eventually perform the validation. The data is still perfectly fine, however, the constraint would need to be altered to match the new data.

Imagine the following:

You are importing into a different character set – from singlebyte to Unicode.
One of your constraints checks the length of a text using byte semantics with the function LENGTHB.
After import into the Unicode database, some characters may take up two bytes or more.
The result of the LENGTHB function would change and you would need to update the constraint definition. Either by allowing more bytes or using LENGTH or LENGTHC.

Let me give you an example:

In my singlebyte database (WE8MSWIN1252), I have a table with these two rows:
- ABC
- ÆØÅ (these are special Danish characters)
In singlebyte all characters take up one byte, so
- LENGTHB('ABC') = 3
- LENGTHB('ÆØÅ') = 3
I migrate to Unicode and now the special Danish character expand. They take up more space in AL32UTF8:
- LENGTHB('ABC') = 3
- LENGTHB('ÆØÅ') = 6
If I have a check constraint using the LENGTHB function, I would need to take this into accout. Plus, there are other similar functions that works on bytes instead of chars, like SUBSTRB.

It’s probably rare to see check constraints using byte semantic functions, like LENGTHB and SUBSTRB. But I’ve seen that in some systems that had to integrate with other systems.

You can end up in a similar situation if you:

use the remap_data option to change data
perform other kinds of data transformation

Since the constraint is still enabled, the database still enforces the constraint for new data after the import.

What’s the Catch?

Validated constraints are very useful to the database because it enables the optimizer to perform query rewrite and potentially improve query performance. Also, index access method might become available instead of full table scans with a validated constraint.

You want to get those constraints validated. But you don’t have to do it during the import. Validating an enabled, not validated constraint does not require a lock on the table. Thus, you can postpone the validation to a later time in your maintenance window, and you can perform other activities at the same time (like backup). Perhaps you can validate constraints while users are testing the database. Or wait until the next maintenance window.

Further, Data Pump always adds validated constraints in these circumstances:

On DEFAULT ON NULL columns
Used by a reference partitioned table
Used by a reference partitioned child table
Table with Primary key OID
Used as clustering key on a clustered table

What About Rely

After import, you could manually add the rely clause:

alter table ... modify constraint ... rely;

Rely tells the database that you know the data is good. The optimizer still doesn’t trust you until you set the parameter QUERY_REWRITE_INTEGRITY to TRUSTED. Now, the optimizer can now benefit from some query rewrite options, but not all of them.

Nothing beats a truly validated constraint!

Validate Constraints Using Parallel Query

Since you want to validate the constraints, Connor McDonald made a video showing you can do that efficiently using parallel query:

Changing the default parallel degree (as shown in the video) might be dangerous in a running system.

All other queries will also run with parallel
ALTER TABLE might lead to cursor invalidation

So, here’s a better approach (thanks Connor):

alter session force parallel query;
alter table ... modify constraint ... enable validate;

The validation happens:
Without table lock
In parallel
And with no cursor invalidation

Nice!

Final Words

If you’re short on time, consider adding constraints as not validated.

The above case with more than 10 hours spent on adding validated constraints; that could have been just a few seconds with novalidate constraints. That’s a huge difference to a time critical migration.

Don’t forget to validate them at one point, because validated constraints are a benefit to the database.

Check my previous blog post for further details on constraint internals.

Appendix

Autonomous Database

The fix is also available in Autonomous Database, both 19c and 23ai.

Zero Downtime Migration

If you import via Zero Downtime Migration (ZDM) you need to add the following to your ZDM response file:

DATAPUMPSETTINGS_METADATATRANSFORMS-1=name:CONSTR_NOVALIDATE,value:1

You might have to change METADATATRANSFORMS-1 to METADATATRANSFORMS-<n> if you have additional transformations (where <n> is a incrementing number).

Published October 3, 2024September 23, 2025

22 thoughts on “How to Speed up Data Pump Imports Using NOVALIDATE Constraints”

David says:

October 28, 2024 at 17:42

Hi Daniel, Hi xxx,
I’ve tried to do a test, but I can’t find the option.

$ORACLE_HOME/OPatch/opatch lspatches
36682332;DATAPUMP BUNDLE PATCH 19.24.0.0.0
36414915;OJVM RELEASE UPDATE: 19.24.0.0.240716 (36414915)
36582781;Database Release Update : 19.24.0.0.240716 (36582781)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.

Import: Release 19.0.0.0.0 – Production on Mon Oct 28 17:33:00 2024Version 19.24.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE,
LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, SEGMENT_CREATION,
STORAGE, and TABLE_COMPRESSION_CLAUSE.

Last week I did a test using the API and the option was not valid.

DBMS_DATAPUMP.METADATA_TRANSFORM (handle => v_hdnl, name => ‘CONSTRAINT_NOVALIDATE’, value => ‘1’);

DLorcaC

LikeLike

Reply
1. Daniel Overby Hansen says:
  
  October 28, 2024 at 21:42
  
  Hi,
  It should work. Which job_mode are you sing? Full, schema, table, or …? Do you have an SR with the full logs?
  Regards,
  Daniel
  
  LikeLike
  
  Reply
2. Daniel Overby Hansen says:
  
  January 28, 2025 at 09:11
  
  I’ve updated the blog post. There is another fix that you must add, 37280692. By 19.27 everything should be fully included in Release Update and Data Pump Bundle Patch (I hope).
  
  Let me know how it turns out,
  Daniel
  
  LikeLike
  
  Reply
Leonardo Carneiro says:

November 2, 2024 at 18:49

Hi Daniel. I’m also unable to use this transform.

Tried

TRANSFORM=LOB_STORAGE:SECUREFILE
TRANSFORM=CONSTRAINT_NOVALIDATE:Y

and also

TRANSFORM=LOB_STORAGE:SECUREFILE,CONSTRAINT_NOVALIDATE:Y

But i get this error in the log:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
ORA-39001: invalid argument value
ORA-39042: invalid transform name CONSTRAINT_NOVALIDATE

Using 19.24 on SOLARIS here.

LikeLike

Reply
1. Daniel Overby Hansen says:
  
  November 3, 2024 at 10:31
  
  Hi Leonardo,
  Thanks for letting me know. I’ll check and get back to you.
  Regards,
  Daniel
  
  LikeLike
  
  Reply
2. Daniel Overby Hansen says:
  
  January 28, 2025 at 09:11
  
  I’ve updated the blog post. There is another fix that you must add, 37280692. By 19.27 everything should be fully included in Release Update and Data Pump Bundle Patch (I hope).
  
  Let me know how it turns out,
  Daniel
  
  LikeLike
  
  Reply
Mario Coronel says:

November 28, 2024 at 17:22

Hi Daniel, when I use transform=constraint novalidate:y in the import on Autonomous in a table I have a field “GENERATED BY DEFAULT ON NULL AS IDENTITY” I get the following error: ORA-30665: invalid NOT NULL constraint specified on a DEFAULT ON NULL column

LikeLike

Reply
1. Daniel Overby Hansen says:
  
  December 1, 2024 at 08:35
  
  Hi,
  Thanks for letting me know. This looks like a bug to me. I’ll report it.
  Regards,
  Daniel
  
  LikeLike
  
  Reply
Maurizio says:

December 11, 2024 at 12:33

Hi,

I have the same problem:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
ORA-39001: invalid argument value
ORA-39042: invalid transform name CONSTRAINT_NOVALIDATE

Did you found a solution?

LikeLike

Reply
1. Daniel Overby Hansen says:
  
  December 11, 2024 at 15:08
  
  Hi,
  Last I heard developers found an issue and are working on fixing it. I hope it will be part of the next Release Update and Data Pump bundle patch. I’m on vacation now, but will check in January when I’m back.
  
  Unfortunately, we haven’t been able to find a workaround.
  
  Regards,
  Daniel
  
  LikeLike
  
  Reply
2. Daniel Overby Hansen says:
  
  January 28, 2025 at 09:10
  
  I’ve updated the blog post. There is another fix that you must add, 37280692. By 19.27 everything should be fully included in Release Update and Data Pump Bundle Patch (I hope).
  
  Let me know how it turns out,
  Daniel
  
  LikeLike
  
  Reply
Parsa Bahrami says:

January 21, 2025 at 02:17

Hi Daniel,

I’m getting the same error as the other commenters here:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
ORA-39001: invalid argument value
ORA-39042: invalid transform name CONSTRAINT_NOVALIDATE

My DB is on 19.25 with 19.25 Datapump Bundle Patch applied:

$ opatch lspatches
36866578;JDK BUNDLE PATCH 19.0.0.0.241015
36917416;OCW RELEASE UPDATE 19.25.0.0.0 (36917416)
37056207;DATAPUMP BUNDLE PATCH 19.25.0.0.0
36878697;OJVM RELEASE UPDATE: 19.25.0.0.241015 (36878697)
36912597;Database Release Update : 19.25.0.0.241015 (36912597)

Have your team been able to fix the bug in the next release?

Thanks,

LikeLike

Reply
1. Daniel Overby Hansen says:
  
  January 22, 2025 at 17:28
  
  Hi,
  
  Thanks for reaching out. Unfortunately, we’ve found a little issue in 19c that we’ve fixed in patch 37280692. You need to request a backport to your Release Update. We hope to include it in 19.27.
  
  Regards,
  Daniel
  
  LikeLike
  
  Reply
2. Daniel Overby Hansen says:
  
  January 28, 2025 at 09:10
  
  I’ve updated the blog post. There is another fix that you must add, 37280692. By 19.27 everything should be fully included in Release Update and Data Pump Bundle Patch (I hope).
  
  Let me know how it turns out,
  Daniel
  
  LikeLike
  
  Reply
Boon says:

July 29, 2025 at 13:31

Hi,

I have upgraded the database from version 19.25.0.0.0 to version 19.28.0.0.0 (the latest avilable at this time) and installed latest DataPump Patch #38170982 (DATAPUMP BUNDLE PATCH 19.28.0.0.0) but when adding TRANSFORM=constraint_novalidate:Y to impdp command, I am still getting ORA-39001: invalid argument value and ORA-39042: invalid transform name CONSTRAINT_NOVALIDATE.

Also I could not find patch 37280692 on the Oracle support website.

Any advice would be appreciated.

LikeLike

Reply
1. Daniel Overby Hansen says:
  
  July 31, 2025 at 10:53
  
  Hi,
  
  In 19.28 with the Data Pump bundle patch it should work without any additional patches.
  
  When you run “impdp” are you doing that from the same Oracle Home as the database? The client – “impdp” – mush also be patched to 19.28 with the bundle patch. If you run “impdp” out of the Oracle home, it is so, but sometimes you run “impdp” from a different home or a remote client.
  
  Regards,
  Daniel
  
  LikeLike
  
  Reply
  1. Boon says:
    
    August 1, 2025 at 08:02
    
    It appears that I hadn’t completed the patching process correctly earlier.
    Following the guidance from the Oracle Support team, I ran the following commands:
    
    $ datapatch
    SQL> @?/rdbms/admin/utlrp.sql
    
    After completing these steps, I re-ran the import command with the TRANSFORM=constraint_novalidate:Y parameter, and it executed successfully.
    
    Thanks!
    
    LikeLiked by 1 person
    
    Reply
    1. Daniel Overby Hansen says:
      
      August 1, 2025 at 09:06
      
      I’m glad that you found the solution. Let me know your results with the transformation. I’m curious to know.
      
      Regards,
      Daniel
      
      LikeLike
      
      Reply
Boon says:

August 12, 2025 at 09:47

To make a long story short, we backed up the database on Oracle 12c (1.5TB of data files were created) and restored it on 19c, where it expanded to approximately 2.3TB on disk; the restore process took 25 hours, but using CONSTRAINT_NOVALIDATE reduced it to 20 hours.

LikeLike

Reply
1. Daniel Overby Hansen says:
  
  August 14, 2025 at 09:24
  
  So, you saved 5 hours on the import. That’s quite good. Still 20 hours for an import sounds like a lot of time.
  
  If you share the import log file with me, we can see if there are other low-hanging fruits. You can share it via daniel.overby.hansen (a) oracle.com.
  
  Regards,
  Daniel
  
  LikeLike
  
  Reply
Dilshan Aththanayake says:

September 24, 2025 at 07:35
Hi Daniel;
- I’m also facing the same issue with 19.28
- DB has been patches to 19.28 and when I try the Import with ”TRANSFORM=constraint_novalidate:y” in the Parfile, still I’m getting the error as :
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
ORA-39001: invalid argument value
ORA-39042: invalid transform name CONSTRAINT_NOVALIDATE
- impdp version also in 19.28 :
Import: Release 19.0.0.0.0 – Production on Wed Sep 24 07:34:57 2025Version 19.28.0.0.0
- Am i missing something here?
Appreciate your input in this!

BR!

/Dilshan

LikeLike
Reply
1. Daniel Overby Hansen says:
  
  September 24, 2025 at 09:13
  
  Hi Dilshan,
  
  Please verify that you have 19.28 plus the matching Data Pump bundle patch installed on the server.
  
  Also, which client (impdp) are you using to start the import? Is it the one from the database Oracle home? If not, then you must ensure your client is patched as well – to 19.28 plus Data Pump bundle patch.
  
  Regards,
  Daniel
  
  LikeLike
  
  Reply