以前沒注意過，今天發現線上重定義表會導致constraint變成novalidate。
解決方法很簡單，就是在dbms_redefinition.copy_table_dependents之後enable validate 中間表的這個constraint。
寫出來引起大家注意。

SQL> create table test1 tablespace DATA01 as select * from dba_objects;
Table created.

SQL> alter table test1 add constraint test1_pk primary key(object_id);
Table altered.

SQL> create table test2 tablespace SYSTEM as select * from dba_objects where 1=2;
Table created.

SQL> select constraint_name, status, VALIDATED from user_constraints where table_name = 'TEST1';
CONSTRAINT STATUS                VALIDATED
---------- ------------------------ ---------------------------------------
TEST1_PK ENABLED                VALIDATED

SQL> exec dbms_redefinition.can_redef_table( USER, 'TEST1', DBMS_REDEFINITION.CONS_USE_PK) ;
PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.START_REDEF_TABLE(USER, 'TEST1', 'TEST2', NULL, DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.

var v_num number
begin
dbms_redefinition.copy_table_dependents(USER, 'TEST1', 'TEST2', dbms_redefinition.cons_orig_params, true, true, true, true, :v_num, true);
dbms_output.put_line('error number: ' || :v_num);
end;
/
error number: 0

SQL> select constraint_name, status, VALIDATED from user_constraints where table_name = 'TEST1';
CONSTRAINT STATUS                VALIDATED
---------- ------------------------ ---------------------------------------
TEST1_PK ENABLED                VALIDATED

SQL> select constraint_name, status, VALIDATED from user_constraints where table_name = 'TEST2';
CONSTRAINT STATUS                VALIDATED
---------- ------------------------ ---------------------------------------
TMP$$_TEST ENABLED                NOT VALIDATED
1_PK0

***這時你發現中間表的constraint並沒有validate，所以如果你直接finish redefinition的話，新的表會有novalidate的constraint。
***當然這樣其實沒多大問題，但是如果這個表時分割槽表，將來做exchange partition時要注意這點，否則會報ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION。

解決辦法很簡單：
SQL> alter table TEST2 enable validate primary key;
Table altered.

SQL>  select constraint_name, status, VALIDATED from user_constraints where table_name = 'TEST2';
CONSTRAINT STATUS                VALIDATED
---------- ------------------------ ---------------------------------------
TMP$$_TEST ENABLED                VALIDATED
1_PK0

SQL> exec dbms_redefinition.sync_interim_table( USER, 'TEST1', 'TEST2');
PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.finish_redef_table( USER, 'TEST1', 'TEST2');
PL/SQL procedure successfully completed.

SQL> select constraint_name, status, VALIDATED from user_constraints where table_name = 'TEST1';
CONSTRAINT STATUS                VALIDATED
---------- ------------------------ ---------------------------------------
TEST1_PK ENABLED                VALIDATED

如果以前沒注意到的同學注意了。

線上重定義表導致constraint變成novalidate

相關文章