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

Question

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?

Answer 1

I've just had a similar issue, and from the ideas above created the following script. If you want the columns in the same order you basically need to create a new set of columns at the end of the table, copy the data, drop the old columns, rename the new columns to the old name. I only had three so not too much hassle.

It's a good idea to check if any dependent objects need recompiling afterwards.

ALTER TABLE APPLICATION_LOG ADD CREATED_ON_NEW DATE;
ALTER TABLE APPLICATION_LOG ADD CREATED_ON_TRUNC_NEW DATE;
ALTER TABLE APPLICATION_LOG ADD CREATED_BY_NEW VARCHAR2(255);
UPDATE APPLICATION_LOG
SET CREATED_ON_NEW = CREATED_ON ,
    CREATED_ON_TRUNC_NEW = CREATED_ON_TRUNC ,
    CREATED_BY_NEW = CREATED_BY 
;
ALTER TABLE APPLICATION_LOG MODIFY CREATED_ON_NEW NOT NULL;
ALTER TABLE APPLICATION_LOG MODIFY CREATED_ON_TRUNC_NEW NOT NULL;
ALTER TABLE APPLICATION_LOG MODIFY CREATED_BY_NEW NOT NULL;
ALTER TABLE APPLICATION_LOG DROP COLUMN CREATED_ON;
ALTER TABLE APPLICATION_LOG DROP COLUMN CREATED_ON_TRUNC;
ALTER TABLE APPLICATION_LOG DROP COLUMN CREATED_BY;
ALTER TABLE APPLICATION_LOG RENAME COLUMN CREATED_ON_NEW TO CREATED_ON;
ALTER TABLE APPLICATION_LOG RENAME COLUMN CREATED_ON_TRUNC_NEW TO CREATED_ON_TRUNC;
ALTER TABLE APPLICATION_LOG RENAME COLUMN CREATED_BY_NEW TO CREATED_BY;

Answer 2

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.

Answer 3

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.

Answer 4

ALTER TABLE YourTable MODIFY YourColumn DEFAULT NULL;