Search notes:

Oracle: DEFAULT clause for columns

create table T (
   …
   col_name varchar2(10) DEFAULT [ON NULL] expr,
   …
);
A column can be assigned an expression which provides the column's default value with which the column is filled in an insert statement if no explicit value is stated.
The expression can be a SQL function (with some limitations), non-constant values such as sysdate, systimestamp or sequence related pseudo columns (currval and nextval).
The expression cannot be
In order to be able to query (rather than insert) non-constant values, virtual columns must be used.

Generate value from a sequence

create sequence tq84_seq;

create table tq84_tab (
   id  number             default tq84_seq.nextval not null,
   txt varchar2(10)
);

insert into tq84_tab      values (999999, 'foo');
-- 1 row inserted

insert into tq84_tab      values (  null, 'bar');
-- cannot insert NULL into (...."TQ84_TAB"."ID")

insert into tq84_tab(txt) values (        'baz');
-- 1 row inserted


select * from tq84_tab;
--         ID TXT
-- ---------- ----------
--     999999 foo
--          1 baz

drop table    tq84_tab purge;
drop sequence tq84_seq;
Github repository Oracle-Patterns, path: /DatabaseObjects/Tables/columns/default/sequence.sql

Altering an existing table

In an existing table, a column can be altered to have a default value like so: 
alter table log_entry modify time_entered  default systimestamp;

Querying the data dictionary

The following select statement queries the data dictionary for columns with default values.
This query also finds columns that were not created with a default clause but as identity columns. 
select
   table_name,
   column_name,
   data_type,
   data_default,
   identity_column
from
   user_tab_cols
where
-- table_name like '%' and
   data_default is not null;

Combining default columns with check constraints

If a column combines a check constraint with a default expression, the check constraint must follow the default expression: 
create table tq84_tab (
   id integer,
   dt date        check (dt = trunc(dt)) default trunc(sysdate) not null
);
--
-- ORA-00907: missing right parenthesis
--
 
create table tq84_tab (
   id integer,
   dt date        default trunc(sysdate) check (dt = trunc(dt)) not null
);
--
-- Table TQ84_TAB created.
--
 
drop table tq84_tab;

Index