Skip to Main Content

CommitDDL throws ORA-01442: column to be modified to NOT NULL is already NOT NULL

binsoftDec 6 2022

I have a strange problem on a fresh 19c database. CommitDDL throws an ORA-01442 for each Not Null constraint on a table. I was able to reproduce the behaviour on three databases which were all setup the same time. But not by me, I have no DBA access. The test script is
Create Table bstest(c1 Number constraint pk primary key, c2 Number Not Null);
exec dbms_wm.enableversioning('bstest');
exec dbms_wm.beginddl('bstest');
exec dbms_wm.commitddl('bstest');
exec dbms_wm.commitddl('bstest', ignore_last_error => True);
exec dbms_wm.disableversioning('bstest');
Drop Table bstest purge;

This gives the following output:
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 6 17:41:58 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Last Successful login time: Mon Dec 05 2022 18:03:42 +01:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
SQL> Create Table bstest(c1 Number constraint pk primary key, c2 Number Not Null);
Table created.
SQL> exec dbms_wm.enableversioning('bstest');
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.beginddl('bstest');
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.commitddl('bstest');
BEGIN dbms_wm.commitddl('bstest'); END;
*
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "WMSYS.LT", line 2886
ORA-06512: at "WMSYS.OWM_DDL_PKG", line 2630
ORA-06512: at "WMSYS.LTUTIL", line 394
ORA-06512: at "WMSYS.LTUTIL", line 367
ORA-06512: at "WMSYS.LTADM", line 8256
ORA-06512: at "WMSYS.LTUTIL", line 354
ORA-06512: at "WMSYS.OWM_DDL_PKG", line 2607
ORA-06512: at "WMSYS.LT", line 2880
ORA-06512: at line 1
SQL> exec dbms_wm.commitddl('bstest', ignore_last_error => True);
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.disableversioning('bstest');
PL/SQL procedure successfully completed.
SQL> Desc bstest;
Name Null? Type


C1 NOT NULL NUMBER
C2 NUMBER

I guess this is a case for support?!
Ralf

Comments
Post Details
Added on Dec 6 2022
1 comment
17 views