解决导入过程中出现的ORA-02289错误

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 解决导入过程中出现的ORA-02289错误

用expdp导出生产库数据到测试库,执行impdp的时候报了ORA-02298错误,提示生效TBL_B表的外键约束FK_B_ID的时候出错,

解决导入过程中出现的ORA-02289错误

看看ORA-02298的错误描述,因为存在独立的字节点记录,导致生效约束操作报错,

解决导入过程中出现的ORA-02289错误

通俗一些,就是子表外键对应的主表主键/唯一约束键值不存在,所以此时无法生效外键约束。

方案1:

既然错误提示子表存在一些主表无记录的外键值,那么只要找出这些不符合主外键关系的子表记录,并且删除这些,保证子表中的外键记录,主表中均有对应的记录。

创建测试表和相应数据,

解决导入过程中出现的ORA-02289错误

主表不存在id=2这条记录,但子表中存在外键字段id_a=2的这条记录,只是由于disable了约束所以才可以insert,但实际此时是无法enable约束,这和上面执行impdp的效果相同,

解决导入过程中出现的ORA-02289错误

使用如下SQL,可以找出子表TBL_B中外键字段id_a的值未在主表TBL_A中有定义的记录,并且删除,

解决导入过程中出现的ORA-02289错误

此时就可以正常enable约束。

使用如下SQL,可以根据子表名称和子表外键约束名称,自动拼接出需要删除子表非法数据的SQL语句,复制出来继续执行就行,

SELECT ' delete from '  || a.table_name  || ' a  where not exists ( select 1 from '  || c_pk.table_name  || ' b  where b.'  || b.column_name  || '=a.'  || a.column_name  ||');' FROM user_cons_columns a JOIN user_constraints c ON a.constraint_name = c.constraint_name JOIN user_constraints c_pk ON c.r_constraint_name = c_pk.constraint_name JOIN user_cons_columns b ON c_pk.constraint_name = b.constraint_name WHERE c.constraint_type = 'R' AND upper(a.table_name) = upper('&Table_Name') AND upper(a.constraint_name) = upper('&FK_NAME');

可以从我的GitHub上下载这一个SQL脚本,

https://github.com/bisal-liu/oracle/blob/832c9c34c068981405a68bae55de885d78cf7bca/solve_illegal_constraint_data

方案2:
出现错误的根本原因,是因为expdp导出的过程中,对于数据表是有DML操作的,即执行expdp指令导出的数据并不能确保属于同一个事务,要从根本解决这问题,就需要确保执行expdp的操作对应的数据属于同一个事务。

exp下可以使用consistent参数,默认值是N,

CONSISTENT cross-table consistency(N)

使用consistent=y,则会设置set transaction read only,即使用了只读事务机制,保证exp导出数据属于一个事务了,

解决导入过程中出现的ORA-02289错误

但其有一些弊端,例如由于需要读取回滚段中未提交的事务数据,因此exp表会变慢,同时官方文档列出了一些使用consistent=y的适用场景以及注意事项,

解决导入过程中出现的ORA-02289错误

expdp下可以使用flashback_scn和flashback_time参数,和闪回表类似,支持设置SCN和TIME两种,

解决导入过程中出现的ORA-02289错误

FLASHBACK_SCN介绍,

解决导入过程中出现的ORA-02289错误

FLASHBACK_TIME介绍,

解决导入过程中出现的ORA-02289错误 解决导入过程中出现的ORA-02289错误

总结:

  1. 解决ORA-02289错误,要理解其本质,即子表外键值存在不属于主表主键/唯一约束键的情况。
  1. 一种方法是手工删除子表中存在的非法数据,保证主子表关系正确。

  2. 一种方法是保证导出的时候就要求数据属于同一事物,不受其他事务的影响,此时exp有consistent参数,expdp有flashback_scn和flashback_time参数可以支持此操作,而且需要清楚用这些参数的原理、弊端,以及适用场景。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

解决导入过程中出现的ORA-02289错误
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 解决导入过程中出现的ORA-02289错误


 上一篇
如何验证dump文件的有效性 如何验证dump文件的有效性
为了测试用途,从生产库导出了一份dump文件,打算导入测试环境中,如果这份dump文件有损坏,可能我执行的impdp,会造成数据库有一部分数据导入成功了,但另一部分失败了,要么挑出失败的部分,断点执行,这难度比较大,要么只能清空,重新执行。
下一篇 
有索引却不用的两个场景 有索引却不用的两个场景
一直有朋友问,是不是表建了索引,一定会使用索引,在RBO时代,访问效率会参考一些规则,优先级高的,认为效率就高,例如索引就比全表扫描效率高,但CBO时代,则会以成本为依据,谁的成本低,谁的效率就高,这样更科学。 建了索引,SQL却未使用索引