The NOT NULL constraint problem of Oracle online redefinition of copy table structure

Before testing and use of the time really did not find this problem, always think that copy_table_dependents will automatically filter not NULL constraints.

However, the fact is that, if you intend to use the Copy_table_dependents procedure to replicate related objects such as indexes, constraints, and permissions, the existence of a NOT NULL constraint raises an error when the target table is established, even if the NOT NULL constraint should be avoided:

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 () 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 being modified to not NULL are already not NULL

Ora-06512:at "SYS. Dbms_redefinition ", line 984

Ora-06512:at "SYS. Dbms_redefinition ", line 1726

Ora-06512:at Line 2

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

In this case, in addition to avoiding specifying NOT NULL when the table is being built, you can avoid the execution of the error abort process by specifying the ignore_errors parameter of the copy_table_dependents procedure to be 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

The above ignores the error caused by the NOT NULL constraint, thus completing the copy process of the table's related objects, but there are also problems with this method, such as the number of errors returned, it is difficult to determine exactly what happened. It's better to let the error message show up more directly.

It doesn't matter if a copy_table_dependents error occurs, you can check the index, constraints, permissions, and triggers on the target table to determine which step is currently executing. For objects that have already been copied, when you run the Copy_table_dependents procedure again, set the corresponding argument to False for this type.

Sql> BEGIN

2 dbms_redefinition. Finish_redef_table (USER, ' T ', ' t_inter ');

3 END;

4/

Pl/sql procedure successfully completed.

When all dependent objects are successfully replicated, you can perform the finish_redef_table process to complete the online redefined operation.

Author: 51cto Blog Oracle Little Bastard