Why does this Oracle DROP COLUMN alter the default value of another column?

Question

We have a weird situation on a table in an Oracle database where dropping a column results in changing the default values of another column. Here's the scenario.

I have my table with some sample data in it :

select * from SAMPLE_TABLE ;

ID                                       BUSINESS_KEY
---------------------------------------- ---------------
e59bf31c-49a4-4638-bf6e-7d1be60f4dbb                   1
c0dabf78-d9ca-4072-832e-aeb618c7ed14                   2

I add column TYPE1 with a check constraint (TYPE1_VAL1 or TYPE1_VAL2) and a default value TYPE1_VAL2 :

alter table SAMPLE_TABLE add TYPE1 varchar(10) default 'TYPE1_VAL2' not null check(TYPE1 in ('TYPE1_VAL1', 'TYPE1_VAL2'));

Table altered.

I see that the default value (TYPE1_VAL2) is correctly filled in:

select * from SAMPLE_TABLE ;

ID                                       BUSINESS_KEY    TYPE1
---------------------------------------- --------------- ----------
e59bf31c-49a4-4638-bf6e-7d1be60f4dbb                   1 TYPE1_VAL2
c0dabf78-d9ca-4072-832e-aeb618c7ed14                   2 TYPE1_VAL2

I add another column TYPE2 with another check constraint (TYPE2_VAL1 or TYPE2_VAL2) and a default value TYPE2_VAL2 :

alter table SAMPLE_TABLE add TYPE2 varchar(15) default 'TYPE2_VAL2' not null check(TYPE2 in ('TYPE2_VAL1', 'TYPE2_VAL2'));

Table altered.

And again see that the default value (TYPE2_VAL2) is correct :

SYSTEM(SYSTEM) @ DB_USER > select * from SAMPLE_TABLE ;

ID                                       BUSINESS_KEY    TYPE1      TYPE2
---------------------------------------- --------------- ---------- ---------------
e59bf31c-49a4-4638-bf6e-7d1be60f4dbb                   1 TYPE1_VAL2  TYPE2_VAL2
c0dabf78-d9ca-4072-832e-aeb618c7ed14                   2 TYPE1_VAL2  TYPE2_VAL2

And now for the weird part. When I drop the first column, it seems to apply the default value from the dropped column onto the remaining column:

ALTER TABLE SAMPLE_TABLE DROP COLUMN TYPE1;

Table altered.

select * from SAMPLE_TABLE ;

ID                                       BUSINESS_KEY    TYPE2
---------------------------------------- --------------- ---------------
e59bf31c-49a4-4638-bf6e-7d1be60f4dbb                   1 TYPE1_VAL2
c0dabf78-d9ca-4072-832e-aeb618c7ed14                   2 TYPE1_VAL2

So where before the TYPE2 column contained TYPE2_VAL2, all of a sudden after the drop it contains TYPE1_VAL2. It's as if the check constraint of the dropped column moved to this column.

This happens on our test environment where we are running Oracle Database 11g Release 11.2.0.4.0 - 64bit Production on Linux.

On our local CentOS / Oracle XE edition we don't have this issue.

Any idea what could cause this and how we can prevent this from happening. Is this by design / a bug / a mistake on our part ?

Answer 1

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.