oracle复制表约束,oracle在线重定义拷贝表结构的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过程来完成在线重定义的操作。