一、问题描述
在对三张具有触发器的表进行导入数据时,一直提示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