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 ?

Mcgrew

Smells like you need to open a SR. – Dibru

This works for me on 11.2.0.4 on Solaris. Looks like a very specific bug. Like Luc M said, you'll probably need to open an SR. (Also, I'm not sure why someone voted to close this question - this is a perfectly valid question for this site.) – Carping

It's probably something to do with the fact that default values are now stored as metadata (although that's not explicitly stated in the documentation, I think it is implied) if you create the default value with a not null constraint. My guess is that there's a bug in your version that messes up the metadata when the column is dropped, leaving your second column pointing at the default value metadata for the first column. – Brookins

what would be interesting is if you made the default for TYPE2 something of length 15 (since you define TYPE2 as length 15 and TYPE1 as length 10), then drop TYPE2, and see if Oracle tries filling in TYPE1 with the (too large) default values of TYPE2...If you get an ora-600, then definitely I'd say a bug ;) – Eruption

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.

Liddy