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
No comments:
Post a Comment