以前没注意过，今天发现在线重定义表会导致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

如果以前没注意到的同学注意了。

ITPub博客

在线重定义表导致constraint变成novalidate

最新文章