专栏首页bisal的个人杂货铺解决导入过程中出现的ORA-02289错误

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

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

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

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

方案1:

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

创建测试表和相应数据,

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

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

此时就可以正常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导出数据属于一个事务了,

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

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

FLASHBACK_SCN介绍,

FLASHBACK_TIME介绍,

总结:

1. 解决ORA-02289错误,要理解其本质,即子表外键值存在不属于主表主键/唯一约束键的情况。

2. 一种方法是手工删除子表中存在的非法数据,保证主子表关系正确。

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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 新增字段的一点一滴技巧

    在Oracle中给表新增字段的需求,已经写了不少文章了,太多的经历告诉我们,一个简单的需求,如果不了解背后的原理,就很可能出现性能问题。

    bisal
  • 函数索引构成虚拟隐藏列

    《enq: TM - contention锁争用的解决》中提到了SYS_NC00004$,理解不是很明白,于是在OTN上发了帖子咨询了下,

    bisal
  • 新增非空约束字段在不同版本中的演进

    开发提了一个数据库变更需求,新增一字段,没有NOT NULL非空约束,但有默认值为NULL。看起来有些奇怪,因为若字段允许NULL,其默认值就是NULL,不用显...

    bisal
  • MySQL学习笔记(二)

    二、SQL基本知识 SQL 是一种典型的非过程化程序设计语言,这种语言的特点是:只指定哪些数据被操纵,至于对这些数据要执行哪些操作,以及这些操作是如何执行的,则...

    mukekeheart
  • 使用Azure Functions玩转Serverless

    通过无服务器计算,开发者无需管理基础结构,从而可以更快构建应用程序。通过无服务器应用程序,将由云服务提供商自动预配、缩放和管理运行代码所需的基础结构。

    kklldog
  • 【俄译】如何减少俄罗斯的盗版软件?

    大数据文摘
  • 在Win10中使用Gazebo9+进行机器人仿真

    还有一些小伙伴反馈我博客翻译或者搬运过程有缺失,为了防止误导,尽力避免此类情况出现:

    zhangrelay
  • 数据库工程师常见面试题

    问题 1: 为什么 group by 和 order by 会使查询变慢? 答: group by 和 order by 操作通常需要创建一个临时表来处理查询...

    葆宁
  • LeetCode 71. 简化路径(栈)

    以 Unix 风格给出一个文件的绝对路径,你需要简化它。或者换句话说,将其转换为规范路径。

    Michael阿明
  • 改代码套取公积金,程序员被判刑!幽灵漏洞再现新变种;中兴通讯发布半年度业绩预告:预亏70亿-90亿元

    据大河客户端报道, 7 月 12 日上午,原信阳市住房公积金管理中心网络科副科长麻某某、原深圳市恒泰丰科技有限公司石家庄分公司职员赵某某因涉嫌贪污罪当庭受审,两...

    程序员宝库

扫码关注云+社区

领取腾讯云代金券