How do I remove the default value from a column in oracle?

A column in a table has a default value of sysdate and I want to change it so it gets no default value, how do I do this?

ALTER TABLE YourTable MODIFY YourColumn DEFAULT NULL;

Joe's answer is correct in the sense that a column with DEFAULT NULL is functionally equivalent to having never defined a default value for that column in the first place: if a column has no default value, inserting a new row without specifying a value for that column results in NULL.

However, Oracle internally represents the two cases distinctly, as can be seen by looking at the ALL_TAB_COLUMNS system view. (This applies to Oracle 10.x, 11.x, and 12.x, and probably to older versions as well.)

1. The case where a column has been created, or ALTERed, with DEFAULT NULL:

   create table foo (bar varchar2(3) default null);
   
   select default_length, data_default from all_tab_columns where table_name='FOO';
   
   => default_length    data_default
      --------------    ------------
      4                 NULL
   
   select dbms_metadata.get_ddl('TABLE','FOO') from dual;
   
   => CREATE TABLE "FOO"
      (    "BAR" VARCHAR(3) DEFAULT NULL
           …
      )
   

2. No default ever specified:

   create table foo (bar varchar2(3));
   
   select default_length, data_default from all_tab_columns where table_name='FOO';
   
   => default_length    data_default
      --------------    ------------
      (null)            (null)
   
   select dbms_metadata.get_ddl('TABLE','FOO') from dual;
   
   => CREATE TABLE "FOO"
      (    "BAR" VARCHAR(3)
           …
      )
   

As shown above, there is an important case where this otherwise-meaningless distinction makes a difference in Oracle's output: when using DBMS_METADATA.GET_DDL() to extract the table definition.

If you are using GET_DDL() to introspect your database, then you will get slightly different DDL output for functionally-identical tables.

This is really quite annoying when using GET_DDL() for version control and comparison among multiple instances of a database, and there is no way to avoid it, other than to manually modify the output of GET_DDL(), or to completely recreate the table with no default value.

The only way to do what you want is to recreate the table.

It is pretty easy to do in Toad, just right click on the table and select "Rebuild Table". Toad will create script which will rename the table and recreate a new table. The script will recreate indexes, constraints, foreign keys, comments, etc... and populate the table with data.

Just modify the script to remove "default null" after the column in question.