記一次將測試數據庫的數據清空，執行truncate引發ORA-02266的問題分析

原文： https://www.enmotech.com/web/detail/1/760/1.html   （復制鏈接，打開瀏覽器即可查看） 

文章轉載自公眾號  bisal的個人雜貨鋪 bisal的個人雜貨鋪 ， 作者 bisal

導讀：將測試數據庫的數據清空，其中涉及主子表的關系，執行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(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的錯誤含義是，“表中的唯一/主鍵被啟用的外鍵引用”，

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.

但是子表已經沒數據了，主表怎么還會提示這個錯誤？究其原因，這和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級別的延遲段特性，

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>'

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的個人雜貨鋪

想了解更多關于數據庫、云技術的內容嗎？

快來關注“數據和云”公眾號、“云和恩墨”官方網站，我們期待與大家一同學習和進步！

（掃描上方二維碼，關注“數據和云”公眾號，即可查看更多科技文章）