How to Remove The Default Value from a Column in Oracle

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 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

How do you drop a default value from a column in a table?

Its a default constraint, you need to perform a:

ALTER TABLE {TableName} 
DROP CONSTRAINT ConstraintName

If you didn't specify a name when you created the constraint, then SQL Server created one for you. You can use SQL Server Management Studio to find the constraint name by browsing to the table, opening its tree node, then opening the Constraints node.

If I remember correctly, the constraint will be named something along the lines of DF_SomeStuff_ColumnName.

EDIT: Josh W.'s answer contains a link to a SO question that shows you how to find the auto generated constraint name using SQL instead of using the Management Studio interface.

Oracle SQL - Change Data Default of a column to NULL - Is there any difference between NULL and (null)?

The (null) in your first two images is SQL Developer showing a dummy value for a true null, so you have something to see. If you go to Tools->Preferences->Database->Advanced there is an option for 'Display Null Value As', which will be set to "(null)". If you change that to something else then that will be show in your quesry output instead.

In the third image the data_default value is not really null, it is the (long) string value 'NULL'.

If you have never set a default for a column then the data_default is really null, but if you set it and then try to unset it then it doesn't revert to a true null value, it is left as whatever value you supply for the modify, as you suppllied it - including the case. Your set it to NULL so it's shown as NULL; if you modified it to null then it would show null.

It doesn't matter here. Essentially what happens is that when you perform an insert and omit that column, the value form the data_default is embedded in the statement. What it actually does under the hood is probably something rather more complicated, but you can think of it like dynamic SQL, with it converting:

'insert into table_name (id) values (42)'

to

'insert into table_name (id, column_name) values (42, '
  || (select data_default from user_tab_columns where column_name = 'COLUMN_NAME')
  || ')'

so it doesn't matter if that ends up as

'insert into table_name (id, column_name) values (42, null)'

or

'insert into table_name (id, column_name) values (42, NULL)'

or

'insert into table_name (id, column_name) values (42, NuLl)'

And before you changed it back to null, it would have been converted to, effectively:

'insert into table_name (id, column_name) values (42, ''0000'')'

You can see that happening if you create a table with a column with no default, then set a default, then set it to null:

create table table_name (id number, column_name varchar2(4));

select column_name, data_type, data_default from user_tab_columns;

select target_name, target_type, property_name,
       (case when regexp_like(property_value, '[a-zA-Z0-9]') then property_value
             else 'primary'
        end) as property_value
from oracle_properties
where target_name like 'DY01EPI%' and
      property_name like 'DataGuardStatus'