Why Does This Oracle Drop Column Alter The Default Value of Another Column

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.

Modify a column to remove or change default value in oracle 11g

Column C2 current definition:

C2 date default sysdate

When you're altering this column to VARCHAR2(10) there is no problem, because Oracle performs an implicit conversion of the values from DATE to VARCHAR2 using current NLS_SETTINGS (date string representation pattern for example)

When you're altering the column to NUMBER(9) Oracle cannot apply an implicit conversion (there is no such rule in its conversion matrix). That's why you're getting an error.

One way to alter is to make the column null:

update t1 set c2 = null;
alter table T1 modify C2 number(9);

Be aware that ALTER is DDL, which means it performs an implicit COMMIT right before and right after the operation.

If you need to save the data in C2 and convert it to NUMBER you can add a new column, make the conversion, drop the old column, and rename the new column:

alter table T1 add C2_new number(9);
update t1 set c2_new = <your conversion of C2>;
alter table T1 drop column C2;
alter table T1 rename column C2_new to C2;

(don't forget about DDL which you cannot ROLLBACK)

Alter table and add new column with default value via a function in Oracle

I think you can achieve it using the default clause on the column but without function (just replace the function call with the content of the function in default clause) as following. (Please note that the User functions are not allowed in the default clause)

ALTER TABLE TEST 
    ADD ID_NUM VARCHAR(255)
    DEFAULT REGEXP_REPLACE(RAWTOHEX(SYS_GUID()), '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})', '\1-\2-\3-\4-\5')
    NOT NULL;

I have prepared the db<>fiddle demo to show you the error with function and success without function.

Cheers!!

Alter table to modify default value of column

Your belief about what will happen is not correct. Setting a default value for a column will not affect the existing data in the table.

I create a table with a column col2 that has no default value

SQL> create table foo(
  2    col1 number primary key,
  3    col2 varchar2(10)
  4  );

Table created.

SQL> insert into foo( col1 ) values (1);

1 row created.

SQL> insert into foo( col1 ) values (2);

1 row created.

SQL> insert into foo( col1 ) values (3);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3

If I then alter the table to set a default value, nothing about the existing rows will change

SQL> alter table foo
  2    modify( col2 varchar2(10) default 'foo' );

Table altered.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3

SQL> insert into foo( col1 ) values (4);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo

Even if I subsequently change the default again, there will still be no change to the existing rows

SQL> alter table foo
  2    modify( col2 varchar2(10) default 'bar' );

Table altered.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo

SQL> insert into foo( col1 ) values (5);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo
         5 bar

---

Related Topics