Why does this Oracle DROP COLUMN alter the default value of another column?
This is an Oracle bug.
It is triggered by adding a column with both a NOT NULL
constraint and a DEFAULT
value to an existing table.
To add the column quickly, Oracle 11g stores the default value in the data dictionary. Oracle calls this "add column optimization".
This is faster than writing out the default value into every table row. The query engine is then supposed to replace any NULL in the table row with the default value from the data dictionary. Unfortunately there are several bugs related to this. Yours appears to be an instance of:
17325413 Drop column with DEFAULT value and NOT NULL definition ends up with dropped column data hitting disk leading to corruption
You can check which columns have been added like this:
select owner, object_name, name from dba_objects, col$ where bitand(col$.PROPERTY,1073741824)=1073741824 and object_id=obj#;
In our case, we were stung by a different bug which returned the incorrect results for a SELECT FOR UPDATE
.
We set parameter _add_col_optim_enabled=FALSE
to turn off this "optimisation". Alternatively, you may be able to upgrade to a later Oracle version where these bugs are resolved.
Upgrading or setting the above parameter will not fix your existing table, which is corrupt. You must re-create that table.