whenever sqlerror exit set serveroutput on set feedback off set verify off set timing on DECLARE l_num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'USER1', orig_table=>'DOCUMENTS', int_table=>'DOCUMENTS_INTERIM', copy_indexes=>0, copy_triggers=>TRUE, copy_constraints=>TRUE, copy_privileges=>TRUE, ignore_errors=>FALSE, num_errors => l_num_errors, copy_statistics=>TRUE, copy_mvlog=>TRUE); DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors); END; /You get the following error:
ORA-01442: column to be modified to NOT NULL is already NOT NULL ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
Cause:
Your interim table has NOT NULL constraints. This is easy to overlook, particulary if you create your interim table using CTAS ("Create Table As Select") statement.
Your interim table should not have any constraints before you execute the copy_table_dependents procedure.
Solution:
Drop the NOT NULL constraint, and retry the operation:
SQL> alter table user1.documents_interim drop constraint SYS_C0018782; Table altered.
Note that you do not have to abort the redefinion procedure at this point, and start all over again.
Simply drop the constraint, and retry your operation.
Thanks a lot !Your trick worked for me !
ReplyDeleteThank you, it worked
ReplyDelete