ORACLE 資料泵impdp匯入報錯之ORA-31693 ORA-04098
一、問題描述
在對三張具有觸發器的表進行匯入資料時，一直提示ORA-31693 ORA-04098報錯，表被跳過無法匯入。後嘗試過truncate表，disable和drop觸發器，匯入均提示ORA-31693 ORA-04098，表的資料無法匯入。最後，使用引數table_exists_action=REPLACE，資料成功匯入。
二、問題現象
因為目標資料庫表已經存在，匯入時使用引數table_exists_action=truncate，然後匯入表的資料，報錯提示如下：
Import: Release 19.0.0.0.0 - Production on Sat Jun 11 08:19:14 2022 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" directory=dir_dump dumpfile=u_tables_03__202206_%U.dump parallel=6 tables= PROD.TAB1001, PROD.TAB1002, PROD.TAB1003 logfile=impdp_tables_03_02_202206.log CLUSTER=n table_exists_action=TRUNCATE Processing object type TABLE_EXPORT/TABLE/TABLE Table " PROD"."TAB1003" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate Table " PROD"."TAB1001" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate Table " PROD"."TAB1002" 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 TABLE_EXPORT/TABLE/TABLE_DATA ORA-31693: Table data object " PROD"."TAB1002" failed to load/unload and is being skipped due to error: ORA-04098: trigger ' PROD.F58C0212 _CFRA_RIA' is invalid and failed re-validation ORA-31693: Table data object " PROD"."TAB1003" failed to load/unload and is being skipped due to error: ORA-04098: trigger ' PROD.F58C0210 _CFRA_RIA' is invalid and failed re-validation ORA-31693: Table data object " PROD"."TAB1001" failed to load/unload and is being skipped due to error: ORA-04098: trigger ' PROD.F58C0104 _CFRA_RIA' is invalid and failed re-validation Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/TRIGGER Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."SYS_IMPORT_TABLE_01" completed with 3 error(s) at Sat Jun 11 08:19:23 2022 elapsed 0 00:00:08
後續問題處理中，將提示的觸發器disable或者drop掉均提示相同的報錯。
三、問題處理
把table_exists_action引數值改成replace引數後，不用disable也不用drop觸發器，就能成功 impdp \"/ as sysdba\" directory=dir_dump dumpfile=u_tables_03_jdedb_202206_%U.dump parallel=6 tables= PROD.TAB1001, PROD.TAB1002, PROD.TAB1003 logfile=impdp_tables_03_202206.log CLUSTER=n table_exists_action=REPLACE [oracle@dbrac1 script]$ impdp \"/ as sysdba\" directory=dir_dump dumpfile=u_tables_03_jdedb_202206_%U.dump parallel=6 tables= PROD.TAB1001, PROD.TAB1002, PROD.TAB1003 logfile=impdp_tables_03_02_202206.log CLUSTER=n table_exists_action=replace Import: Release 19.0.0.0.0 - Production on Sat Jun 11 08:33:25 2022 Version 19.6.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" directory=dir_dump dumpfile=u_tables_03_jdedb_202206_%U.dump parallel=6 tables= PROD.TAB1001, PROD.TAB1002, PROD.TAB1003 logfile=impdp_tables_03_02_202206.log CLUSTER=n table_exists_action=replace Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported " PROD"."TAB1003" 1013. KB 1192 rows . . imported " PROD"."TAB1002" 26.18 MB 25844 rows . . imported " PROD"."TAB1001" 90.04 MB 161139 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/TRIGGER Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."SYS_IMPORT_TABLE_01" completed with 12 error(s) at Sat Jun 11 08:33:48 2022 elapsed 0 00:00:20
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29357786/viewspace-2900075/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- Impdp資料泵匯入
- 使用資料泵impdp匯入資料
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- 資料泵匯入報錯impdp ora-31693 ora-31640 ora-19505 ora-27037
- 資料泵IMPDP 匯入工具的使用
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- oracle 10g資料泵之impdp-同時匯入多個檔案Oracle 10g
- Oracle資料泵－schema匯入匯出Oracle
- Oracle資料泵的匯入和匯出Oracle
- Oracle使用資料泵匯出匯入表Oracle
- oracle10G新特性之資料泵匯出/匯入Oracle
- oracle匯入匯出之expdp/impdpOracle
- 轉oracle資料泵匯出時報錯Oracle
- oracle10g expdp資料泵的bug,按schema匯出，匯入impdp時無jobOracle
- 資料泵匯入分割槽表統計資訊報錯（七）
- 資料泵匯入分割槽表統計資訊報錯（二）
- 資料泵匯入分割槽表統計資訊報錯（四）
- 資料泵匯入分割槽表統計資訊報錯（三）
- 資料泵匯入分割槽表統計資訊報錯（六）
- 資料泵匯入分割槽表統計資訊報錯（五）
- Oracle使用資料泵在異機之間匯出匯入表Oracle
- 資料泵匯出匯入
- Oracle10g 資料泵匯出命令impdp 使用總結Oracle
- Oracle IMPDP匯入資料案例之注意事項（undo/temp）Oracle
- 資料泵(expdp,impdp)高版本匯入低版本操作例項
- 資料泵的匯入匯出
- 資料泵匯出匯入表
- 資料泵 impdp 操作
- Oracle使用資料泵在異機之間匯出匯入多個 schemaOracle
- rac環境下使用impdp匯入資料出錯
- Oracle expdp/impdp匯出匯入命令及資料庫備份Oracle資料庫
- Oracle expdp/impdp匯入匯出備份資料庫指令碼Oracle資料庫指令碼
- 資料泵匯出匯入資料標準文件
- 資料泵取匯出和匯入（一）
- oracle之資料泵Oracle
- Oracle用資料泵匯入資料包12899的錯誤碼解決方法Oracle
- IMPDP匯入遠端資料庫資料庫