执行truncate抛出的ORA-02266

灰太狼 2021-12-13 01:07 211阅读 0赞

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

![640?wx\_fmt=png][640_wx_fmt_png]

最直观的做法，就是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.
    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.
    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 keys1);
    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的错误含义是，“表中的唯一/主键被启用的外键引用”，

02262, 00000, "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.00000, "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...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;...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. 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...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_nameFROM user_constraintsWHERE table_name = '<table_you_are_trying_to_drop>'AND constraint_type = 'P';SELECT *FROM user_constraintsWHERE constraint_type = 'R'AND r_constraint_name = '<constraint_name_returned_above>';
    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 ainner join user_cons_columns b on a.constraint_name = b.constraint_namewhere a.r_constraint_name='主键约束名称';select c.TABLE_NAMEfrom all_constraints p, all_constraints cwhere 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;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. 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. 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操作是可以正常删除主表，只是不适合数据量很大的场景。

[640_wx_fmt_png]: /images/20211210/f1495c02d76a415397d7aac9e708872b.png