線上重定義拷貝表結構的NOT NULL約束問題

以前測試和使用的時候還真的沒發現這個問題，一直認為COPY_TABLE_DEPENDENTS會自動過濾NOT NULL約束。

 

 

然而事實併發如此，如果打算使用COPY_TABLE_DEPENDENTS過程複製索引、約束以及許可權等相關物件，那麼在建立目標表的時候，即使是NOT NULL約束都應該避免，否則NOT NULL約束的存在會引發錯誤：

SQL> CREATE TABLE T AS
  2  SELECT ROWNUM ID, OBJECT_NAME NAME, OBJECT_TYPE TYPE
  3  FROM USER_OBJECTS A;

Table created.

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

Table altered.

SQL> ALTER TABLE T MODIFY NAME NOT NULL;

Table altered.

SQL> CREATE TABLE T_INTER
  2  (ID NUMBER, NAME VARCHAR2(30) NOT NULL, TYPE VARCHAR2(30))
  3  PARTITION BY HASH (ID)
  4  PARTITIONS 4;

Table created.

SQL> SET SERVEROUT ON SIZE 1000000
SQL> BEGIN
  2     DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2     DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_INTER');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> VAR V_NUM NUMBER
SQL> BEGIN
  2     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(USER, 'T', 'T_INTER',
  3             DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, FALSE, :V_NUM, TRUE);
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 984
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1726
ORA-06512: at line 2

對於這種情況，除了避免在建表的時候指定NOT NULL之外，還可以通過指定COPY_TABLE_DEPENDENTS過程的IGNORE_ERRORS引數為TRUE的方式，來避免錯誤中止過程的執行。

SQL> BEGIN
  2     DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(USER, 'T', 'T_INTER',
  3             DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, :V_NUM, TRUE);
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> PRINT V_NUM

     V_NUM
----------
         1

上面就忽略了NOT NULL約束引發的錯誤，從而完成了表的相關物件的複製過程，但是這種方法也存在問題，比如返回的錯誤數很多，就很難判斷到底發生了哪些錯誤。這時不如讓錯誤資訊顯示出來更直接一些。

即使COPY_TABLE_DEPENDENTS出現了錯誤也無所謂，可以檢查目標表上的索引、約束、許可權和觸發器的情況，判斷當前執行到哪一步報錯。對於已經拷貝的物件，再次執行COPY_TABLE_DEPENDENTS過程時，將這種型別對應的引數置為FALSE即可。

SQL> BEGIN
  2     DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T', 'T_INTER');
  3  END;
  4  /

PL/SQL procedure successfully completed.

當所有依賴物件都成功的複製完成，可以執行FINISH_REDEF_TABLE過程來完成線上重定義的操作。