oracle transform=constraint novalidate:y oracle >19.27

      


transform=constraint_novalidate:y 

Data Pump (impdp) parameter speeds up imports 
by creating constraints as ENABLE NOVALIDATE. 
This allows constraints to be created instantly without performing full table scans to 
validate existing data, making it ideal for large data loads



test case !!!!!!!!!!!!


SQL> alter session set container=vihaan ;

Session altered.

SQL> grant dba to test4 identified by test4;

Grant succeeded.


CREATE TABLE test4.DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;


CREATE TABLE test4.EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES test4.DEPT);





INSERT INTO test4.DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO test4.DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO test4.DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO test4.DEPT VALUES(40,'OPERATIONS','BOSTON');


INSERT INTO test4.EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO test4.EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO test4.EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO test4.EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO test4.EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO test4.EMP VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO test4.EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO test4.EMP VALUES(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO test4.EMP VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO test4.EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO test4.EMP VALUES(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO test4.EMP VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO test4.EMP VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO test4.EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);





create or replace directory data_pump_dir1 as '/home/oracle';
grant read on directory data_pump_dir1 to test4 ;
grant write on directory data_pump_dir1 to test4 ;



expdp test4/test4@vihaan directory=data_pump_dir1 dumpfile=EXPDP_test4.dmp logfile=expdp_test4.log schemas=test4 estimate=STATISTICS CLUSTER=N full=N status=10 metrics=Y EXCLUDE=STATISTICS



Worker 1 Status:
  Instance ID: 1
  Instance name: orcl
  Host name: oracent19c
  Access method: direct_path
  Object start time: Tuesday, 05 May, 2026 14:58:38
  Object status at: Tuesday, 05 May, 2026 14:58:42
  Process Name: DW00
  State: WORK WAITING
Job "TEST4"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue May 5 14:58:42 2026 elapsed 0 00:01:29



[oracle@oracent19c admin]$ impdp system/sys@vihaan directory=data_pump_dir1 dumpfile=EXPDP_test4.dmp logfile=expdp_test4.log  schemas=test4 transform=constraint_novalidate:y CLUSTER=N full=N status=10 metrics=Y EXCLUDE=STATISTICS



this option valid from >19.27
import with transform=constraint_novalidate:y <<<<



[oracle@oracent19c admin]$ opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"
Patch  39196236     : applied on Sat May 02 15:45:47 BST 2026
Patch  39034528     : applied on Sat May 02 14:17:50 BST 2026
Patch  29585399     : applied on Thu Apr 18 08:21:33 BST 2019

check below data patch 
[oracle@oracent19c admin]$ opatch lsinventory |grep -i "Database Release Update"
Patch description:  "Database Release Update : 19.31.0.0.260421 (39034528)"



impdp test4/test4@vihaan directory=data_pump_dir1 dumpfile=EXPDP_test4.dmp logfile=expdp_test4.log  schemas=test4 transform=constraint_novalidate:y CLUSTER=N full=N status=10 metrics=Y EXCLUDE=STATISTICS



Worker 1 Status:
  Instance ID: 1
  Instance name: orcl
  Host name: oracent19c
  Object start time: Tuesday, 05 May, 2026 15:04:38
  Object status at: Tuesday, 05 May, 2026 15:04:39
  Process Name: DW00
  State: WORK WAITING
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue May 5 15:04:39 2026 elapsed 0 00:00:12

[oracle@oracent19c admin]$ impdp system/sys@vihaan directory=data_pump_dir1 dumpfile=EXPDP_test4.dmp logfile=expdp_test4.log  schemas=test4 transform=constraint_novalidate:y CLUSTER=N full=N status=10 metrics=Y EXCLUDE=STATISTICS




set linesize 300 
define SCHEMA_NAME='TEST4'
col owner for a20 
col constraint_name for a20
col status  for a20
col  validated  for a20
col  type for a20
col CONSTRAINT_TYPE for a14
col table_name for a20
SELECT owner, table_name ,constraint_name,
CONSTRAINT_TYPE,
 decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
status, validated FROM cdb_constraints
where 1=1
and owner = '&SCHEMA_NAME' 
; 



OWNER                TABLE_NAME           CONSTRAINT_NAME      CONSTRAINT_TYP TYPE                 STATUS            VALIDATED
-------------------- -------------------- -------------------- -------------- -------------------- -------------------- --------------------
TEST4                EMP                  FK_DEPTNO            R              Foreign Key          ENABLED           NOT VALIDATED
TEST4                DEPT                 PK_DEPT              P              Primary Key          ENABLED           NOT VALIDATED
TEST4                EMP                  PK_EMP               P              Primary Key          ENABLED           NOT VALIDATED


 alter session set container=VIHAAN ;
Session altered.


--- to ENABLE VALIDATE


SET SERVEROUTPUT ON
BEGIN
    FOR rec IN (SELECT owner, table_name, constraint_name
                FROM all_constraints
                WHERE owner = 'TEST4'
                AND validated = 'NOT VALIDATED')
    LOOP
        -- This helps you see which table is failing
        DBMS_OUTPUT.PUT_LINE('Processing: ' || rec.owner || '.' || rec.table_name);
        
        EXECUTE IMMEDIATE 'ALTER TABLE "' || rec.owner || '"."' || rec.table_name || 
                          '" MODIFY CONSTRAINT "' || rec.constraint_name || '" ENABLE VALIDATE';
    END LOOP;
END;
/



OWNER                TABLE_NAME           CONSTRAINT_NAME      CONSTRAINT_TYP TYPE                 STATUS            VALIDATED
-------------------- -------------------- -------------------- -------------- -------------------- -------------------- --------------------
TEST4                EMP                  FK_DEPTNO            R              Foreign Key          ENABLED           VALIDATED
TEST4                DEPT                 PK_DEPT              P              Primary Key          ENABLED           VALIDATED
TEST4                EMP                  PK_EMP               P              Primary Key          ENABLED           VALIDATED