暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

一次执行truncate触发ORA-02266解决过程

数据和云 2019-07-24
1001


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



最直观的做法,就是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(1);
1 row created.

SQL> insert into b_1 values(1,1);
1 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 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys



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


0226200000"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=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=873 siz=0 off=24
  kxsbbbfp=7f2df926afc8  bln=32  avl=03  flg=01
  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级别的延迟段特性

gment_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操作是可以正常删除主表,只是不适合数据量很大的场景。


扩展阅读



  1. 美女DBA带你了解PostgreSQL用户及角色

  2. 分布式锁用Redis坚决不用Zookeeper?

  3. 频发:记ADG备库日志应用延迟的一次故障处理-云和恩墨技术通讯精选

  4. MySQL锁的灵魂七拷问

  5. 从Oracle到PostgreSQL:最全控制文件


数据和云

ID:OraNews

如有收获,请划至底部,点击“在看”,谢谢!



公司简介  | 招聘 | DTCC | 数据技术嘉年华 | 免费课程 | 入驻华为严选商城

  

zCloud | SQM | Bethune Pro2 zData一体机 | MyData一体机 | ZDBM 备份一体机

Oracle技术架构 | 免费课程 | 数据库排行榜 | DBASK问题集萃 | 技术通讯 

升级迁移 | 性能优化 | 智能整合 安全保障 |  架构设计 | SQL审核 | 分布式架构 | 高可用容灾 | 运维代维

云和恩墨大讲堂 | 一个分享交流的地方

长按,识别二维码,加入万人交流社群


请备注:云和恩墨大讲堂


文章转载自数据和云,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论