복사되었습니다!
OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.31.0.0
에러 : ORA-39042: invalid transform name CONSTRAINT_NOVALIDATE
impdp CONSTRAINT_NOVALIDATE 옵션 사용시 발생하는 에러
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ impdp system/oracle \
DIRECTORY=dp_test_dir \
DUMPFILE=expdp_validate_test.dmp \
LOGFILE=impdp_user4_novalidate.log \
REMAP_SCHEMA=user1:user4 \
TABLES=user1.emp_test \
TABLE_EXISTS_ACTION=TRUNCATE \
TRANSFORM=CONSTRAINT_NOVALIDATE:Y
Import: Release 19.0.0.0.0 - Production on Fri May 29 23:58:48 2026
Version 19.31.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
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
|
이 에러는 매번 발생하는것은 아니고 특정 상황에서 발생한다고 함
해결 방법 : 데이터펌프 번들 패치 적용
19.31 버전 db이기 때문에 39196236 패치 다운로드 및 적용이 필요함
다운로드 : https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=39196236
적용
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
$ cd /app/oracle/media
$ unzip -q p39196236_1931000DBRU_Generic.zip
$ cd /app/oracle/media/39196236/
$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.51
Copyright (c) 2026, Oracle Corporation. All rights reserved.
Oracle Home : /app/oracle/product/19c
Central Inventory : /app/oraInventory
from : /app/oracle/product/19c/oraInst.loc
OPatch version : 12.2.0.1.51
OUI version : 12.2.0.7.0
Log file location : /app/oracle/product/19c/cfgtoollogs/opatch/opatch2026-05-30_00-10-25AM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 39196236
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '39196236' to OH '/app/oracle/product/19c'
Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...
Patching component oracle.rdbms, 19.0.0.0.0...
Patch 39196236 successfully applied.
Log file location: /app/oracle/product/19c/cfgtoollogs/opatch/opatch2026-05-30_00-10-25AM_1.log
OPatch succeeded.
|
opatch apply가 완료됨
확인
|
1
2
3
4
5
6
|
$ opatch lspatches
39196236;DATAPUMP BUNDLE PATCH 19.31.0.0.0
39034528;Database Release Update : 19.31.0.0.260421 (39034528)
35967489;OCW RELEASE UPDATE 19.22.0.0.0 (35967489)
OPatch succeeded.
|
DATAPUMP BUNDLE PATCH 19.31이 적용됨
datapatch 적용
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
$ datapatch -verbose
SQL Patching tool version 19.31.0.0.0 Production on Sat May 30 00:16:42 2026
Copyright (c) 2012, 2026, Oracle. All rights reserved.
Log file for this invocation: /app/oracle/cfgtoollogs/sqlpatch/sqlpatch_15923_2026_05_30_00_16_42/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 39196236 (DATAPUMP BUNDLE PATCH 19.31.0.0.0):
Binary registry: Installed
SQL registry: Not installed
Current state of release update SQL patches:
Binary registry:
19.31.0.0.0 Release_Update 260426152757: Installed
SQL registry:
Applied 19.31.0.0.0 Release_Update 260426152757 successfully on 25-MAY-26 12.55.52.086828 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
No interim patches need to be rolled back
No release update patches need to be installed
The following interim patches will be applied:
39196236 (DATAPUMP BUNDLE PATCH 19.31.0.0.0)
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 39196236 apply: SUCCESS
logfile: /app/oracle/cfgtoollogs/sqlpatch/39196236/28705537/39196236_apply_ORA19FS_2026May30_00_17_26.log (no errors)
SQL Patching tool complete on Sat May 30 00:18:57 2026
|
datapatch 완료됨
다시 impdp CONSTRAINT_NOVALIDATE 옵션 사용하여 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
$ impdp system/oracle \
DIRECTORY=dp_test_dir \
DUMPFILE=expdp_validate_test.dmp \
LOGFILE=impdp_user4_novalidate.log \
REMAP_SCHEMA=user1:user4 \
TABLES=user1.emp_test \
TABLE_EXISTS_ACTION=TRUNCATE \
TRANSFORM=CONSTRAINT_NOVALIDATE:Y
Import: Release 19.0.0.0.0 - Production on Sat May 30 00:19:49 2026
Version 19.31.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** DIRECTORY=dp_test_dir DUMPFILE=expdp_validate_test.dmp LOGFILE=impdp_user4_novalidate.log REMAP_SCHEMA=user1:user4 TABLES=user1.emp_test TABLE_EXISTS_ACTION=TRUNCATE TRANSFORM=CONSTRAINT_NOVALIDATE:Y
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "USER4"."EMP_TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USER4"."EMP_TEST" 660.7 MB 5000000 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Sat May 30 00:20:58 2026 elapsed 0 00:01:06
|
에러가 발생하지 않고 잘 수행됨
하지만 direct path 방식이 아니라 external table 방식으로 동작해 속도가 CONSTRAINT_NOVALIDATE 옵션을 미사용 했을때와 동일했음
참고 : 오라클 19c datapump constraint_novalidate 옵션 테스트 ( http )
원인 : 버그
버그로 인해 발생한 문제
참조 :
Data Pump Recommended Proactive Patches For 19.10 and Above(KB107134)
ORA-39042: invalid transform name CONSTRAINT_NOVALIDATE when Using impdp with TRANSFORM=CONSTRAINT_NOVALIDATE:Y(KB868513)
19c DataPump New Features(KB94989)