数据泵导入报错 ORA-31693 ORA-00604 ORA-00942

数据泵导入报错 ORA-31693 ORA-00604 ORA-00942

一、背景说明

1.数据库版本
11.2.0.4.0

2.操作说明
数据泵导入报错，这个用户下的部分表导入失败，表结构已经导过去，数据没有导过去

3.报错提示
ORA-31693: Table data object "xxx"."xxx" failed to load/unload and is beging skipped due to error;
ORA-00604: error occurred at recursive SQL level 2;
ORA-00942: table or view does not exist;

二、报错说明

我在网上查了一下，描述说是触发了oracle的BUG

详情参考
https://blog.csdn.net/cuilingqiao0657/article/details/100492532

三、解决办法

根据上个参考链接给出的办法执行sql报错

alter database drop supplemental log data;

ERROR at line 1:

ORA-32589: unable to drop minimal supplemental logging

#因为需要做一些调整才能删除，下面是我整理的步骤

1.查看其他日志是否有依赖最小日志的

select supplemental_log_data_min min, supplemental_log_data_pk pk, supplemental_log_data_ui ui, supplemental_log_data_fk fk, supplemental_log_data_all al, supplemental_log_data_pl pl from v$database;

MIN      PK  UI  FK  AL  PL
-------- --- --- --- --- ---
YES      YES YES NO  YES NO

2.删除其他日志

alter database drop supplemental log data (all) columns;

alter database drop supplemental log data (unique) columns;

alter database drop supplemental log data (primary key) columns;

3.再次检查他们的状态。
select supplemental_log_data_min min, supplemental_log_data_pk pk, supplemental_log_data_ui ui, supplemental_log_data_fk fk, supplemental_log_data_all al, supplemental_log_data_pl pl from v$database;

MIN      PK  UI  FK  AL  PL
-------- --- --- --- --- ---
YES      NO  NO  NO  NO  NO

4.现在可以删除最小的补充日志。
alter database drop supplemental log data;

删除最小日志后，再导入使用数据泵导入数据就不会报错了