While performing impdp operations
in the Oracle database, Oracle performs validation checks for every constraint
on the imported table, that can cause a significant delay in importing data.
All new Oracle 23ai, introduces new feature called NOVALIDATE CONSTRAINTS In
this article, we’ll cover its functionality, benefits, practical usage, and the
scenarios where `NOVALIDATE` can be used.
The `NOVALIDATE` constraint is a
Data Pump Import (IMPDP) parameter used to import table constraints without
performing the validation of data against those constraints.
this constraints_novalidate
parameter needs to be used along with the transform parameter in the impdp
utility,
This option restores constraints
in "ENABLED NOT VALIDATED" status.
syntax
TRANSFORM=constraints_novalidate=y
This parameter helps to increase
speed significantly. This parameter only skips data validation during import,
but still applies during normal operations in the database.
Core benefits
- Improved Performance
- Handling Inconsistent Data
- Flexibility in Migration
General syntax of the impdp
command with constraints_validate
impdp
<username>/<password> DIRECTORY=<directory_object>
DUMPFILE=<dumpfile_name> LOGFILE=<logfile_name>
TABLES=<table_name> CONSTRAINTS=NOVALIDATE
or
impdp
<username>/<password> DIRECTORY=<directory_object>
DUMPFILE=<dumpfile_name> LOGFILE=<logfile_name>
TABLES=<table_name> TRANSFORM=constraints_novalidate=y
This NOVALIDATE is useful in below
scenario
- Large Data Migrations
- Mismatched Source and Target Data
- Gradual Data Validation
- Parallel Data Loads
Risks
- Data Integrity Issues
- Delayed Error Detection
- Potential Application Failures
- Manual Cleanup Required
This parameter is really useful when we compare things with older versions.