专栏首页数据和云执行truncate引发ORA-02266的问题分析

执行truncate引发ORA-02266的问题分析

墨墨导读:将测试数据库的数据清空,其中涉及主子表的关系,执行truncate产生的ORA-02266问题处理过程。

开发提了个需求,要求将测试数据库的数据清空,其中涉及主子表的关系,如下所示,

最直观的做法,就是truncate表。首先truncate各个子表,但是当执行truncate主表的时候,提示错误,ORA-02266: unique/primary keys in table referenced by enabled foreign keys。

子表此时没数据,为何不让删除主表的数据?

我们模拟下过程,首先创建测试表,主表a_1,子表b_1,

SQL> create table a_1 (id number);
Table created.

SQL> create table b_1 (id number, id_a_1 number);
Table created.

SQL> alter table a_1 add constraint pk_a_1 primary key(id);
Table altered.

SQL> alter table b_1 add constraint fk_b_a foreign key (id_a_1) references a_1 (id);
Table altered.

此时truncate子表和主表,均会成功,

SQL> truncate table b_01;
Table truncated.

SQL> truncate table a_01;
Table truncated.

但是,当主子表有数据,truncate子表,再做truncate主表的操作,就会提示ORA-02266的错误,

SQL> insert into a_1 values();
 row created.

SQL> insert into b_1 values(,);
 row created.

SQL> commit;
Commit complete.

SQL> truncate table b_1;
Table truncated.

SQL> truncate table a_1;
truncate table a_1
               *
ERROR at line :
ORA-: unique/primary keys in table referenced by enabled foreign keys

ORA-02262的错误含义是,“表中的唯一/主键被启用的外键引用”,

, , "ORA-%s occurs while type-checking column default value expression"
// *Cause:  New column datatype causes type-checking error for existing column
//        default value expression.
// *Action: Remove the default value expression or don't alter the column
//        datatype.

但是子表已经没数据了,主表怎么还会提示这个错误?究其原因,这和truncate操作有关,因为truncate是DDL,但是DDL语句不会检查约束,换句话说,他不知道子表有没有数据依赖于他,索性不让做了。

但是有个疑问,当主子表无数据,此时truncate主表,是可以成功的,只有当主子表有数据的时候,truncate主表才会提示。我看了下统计信息中,表中无数量记录,Oracle是如何知道当前表有数据,禁止truncate?

我猜测,可能是延迟段的影响?

1.看下表中有数据,执行truncate产生的10046,其中truncate table a_1主表时,有个绑定变量的参数是B_1,推测由此知道a_1有外键引用,进而报错,err=2266,

...
LOCK TABLE "A_1" IN EXCLUSIVE MODE  NOWAIT
...
truncate table a_1
...
Bind#1
  oacdty= mxl=() mxlc= mal= scl= pre=
  oacflg= fl2= frm= csi= siz= off=
  kxsbbbfp=f2df926afc8  bln=  avl=  flg=
  value="B_1"
...
ERROR #139835430202688:err=2266 tim=1562853681567125
...

2.看下表中无数据,执行truncate产生的10046,发现他会检索deferred_stg$视图,truncate是靠aw_trunc_proc存储过程,

...
LOCK TABLE "A_1" IN EXCLUSIVE MODE  NOWAIT
...
select pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg, maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg, maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg, cmpflag_stg, cmplvl_stg from deferred_stg$ where obj# =:1
...
truncate table a_1
...
BEGIN
  aw_trunc_proc(ora_dict_obj_type, ora_dict_obj_name, ora_dict_obj_owner);
END;
...

3.关闭session级别的延迟段特性,

SQL> alter session set deferred_segment_creation=false;
Session altered.

表中无数据,执行truncate产生的10046,和上面两个比,操作最简单,LOCK表,执行truncate,没其他操作了,

...
LOCK TABLE "A_1" IN EXCLUSIVE MODE  NOWAIT
...
truncate table a_1
...

从现象看,不是延迟段特性,导致两者的区别,需要请大佬指教。

针对ORA-02266的错误,有几种解决方案,

方案1,禁用约束-truncate-启用约束

可以参考MOS这篇文章《OERR: ORA-2266 "unique/primary keys in table referenced by enabled foreign keys" Reference Note (Doc ID 19499.1)》。

1. 找出主表的约束

SELECT constraint_name
FROM user_constraints
WHERE table_name = '<table_you_are_trying_to_drop>'
AND constraint_type = 'P';

SELECT *
FROM user_constraints
WHERE constraint_type = 'R'
AND r_constraint_name = '<constraint_name_returned_above>';

或者执行下列任意一个SQL,都可以得到主键相关的外键参考,

select a.constraint_type,a.table_name,a.status, b.table_name,b.column_name,b.constraint_name from user_constraints a
inner join user_cons_columns b on a.constraint_name = b.constraint_name
where a.r_constraint_name='主键约束名称';

select c.TABLE_NAME
from all_constraints p, all_constraints c
where p.table_name = '主键约束名称'
and p.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
and c.OWNER=SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
and c.constraint_type = 'R'
and p.CONSTRAINT_NAME = c.R_CONSTRAINT_NAME;

2. 删除约束

SQL> alter table a_1 disable primary key cascade;
Table altered.

3. 执行truncate

4. 启用约束

只是需要注意,enable恢复主键的操作,并不会自动enable外键,需要手工enable外键,

SQL> alter table tbl_a enable primary key;
Table altered.

SQL> alter table tbl_b enable constraint fk_b_a;
Table altered.

方案2,delete删除

使用delete,DML操作是可以正常删除主表,只是不适合数据量很大的场景。

出处:bisal的个人杂货铺

请备注:云和恩墨大讲堂

本文分享自微信公众号 - 数据和云(OraNews)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-07-14

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 杨廷琨Oracle Code大会分享:如何编写高效SQL(含PPT)

    2018 Oracle Code 于5月17日在新加坡拉开帷幕。作为全球开发者交流分享的年度盛会,为吸引所有领域的开发者,Oracle今年将自1996年开始的J...

    数据和云
  • 验证GaussDB T 闪回事务查询功能;闪回表功能强劲闪回TRUNCATE

    总的来说,gaussdb100 T 是可以支持闪回事务查询。 二、GaussDB T 的 Flashback Table 功能非常强劲可以闪回TRUNCATE...

    数据和云
  • SQL 审核 - z3 产品理念与功能介绍

    我们都知道,在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的,一个开发环境中,众多的程序员难免引入一个又一个的或初级或高端的...

    数据和云
  • 执行truncate抛出的ORA-02266

    最直观的做法,就是truncate表。首先truncate各个子表,但是当执行truncate主表的时候,提示错误,ORA-02266: unique/prim...

    bisal
  • BigDecimal类型divide报错

    java.lang.ArithmeticException: Non-terminating decimal expansion; no exact repre...

    Java架构师历程
  • Weblogic漏洞反序列化(CVE-2019-2725)分析

    4月17日,国家信息安全漏洞共享平台(CNVD)公开了Weblogic反序列化远程代码执行漏洞(CNVD-C-2019-48814)。由于在反序列化处理输入信息...

    FB客服
  • ORA-01113问题的简单分析(r6笔记第3天)

    在启动数据库的时候,open阶段总是可能出现各种各样的问题, 比如让人胆战心惊的错误。 ORA-01113: file 1 needs media recov...

    jeanron100
  • python异常处理的哲学

    所谓异常指的是程序的执行出现了非预期行为,就好比现实中的做一件事过程中总会出现一些意外的事。异常的处理是跨越编程语言的,和具体的编程细节相比,程序执行异常的处理...

    py3study
  • python异常处理

    异常处理是工作中编写代码必须要完成的内容,对于不符合预期的用户操作或数据输入,程序总会出现异常情况,而对异常情况能够妥善处理,是保证程序稳定性的关键工作...

    致码DevOps
  • 困扰开发人员的八大代码异常问题

    代码异常对于每个开发人员时时刻刻要面对的问题,兴奋的写完一段代码,信心满满的运行一下,结果一个异常给你当头 一棒,你还得意吗?异常处理的好不好,考验着每个开发人...

    用户1289394

扫码关注云+社区

领取腾讯云代金券