Query below lists:

(A) all default values on tables accessible to the current user in Oracle database

(B) all default values on tables in Oracle database

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select col.owner as schema_name,
       col.table_name,       
       col.column_name,
       col.column_id,
       col.data_default as default_definition
from sys.all_tab_columns col
inner join sys.all_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
-- excluding some Oracle maintained schemas
where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS', 
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
and col.data_default is not null
order by t.owner, t.table_name, col.column_id;

B. If you have privilege on dba_tab_columns and dba_tables

select col.owner as schema_name,
       col.table_name,       
       col.column_name,
       col.column_id,
       col.data_default as default_definition
from sys.dba_tab_columns col
inner join sys.dba_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name
-- excluding some Oracle maintained schemas
where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS', 
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
and col.data_default is not null
order by t.owner, t.table_name, col.column_id;

Columns

• schema_name - schema name, table owner
• table_name - name of the table
• column_name - name of the column
• column_id - sequence number of the column as created
• default_definition - default value definition

Rows

• One row represents one column that has default value defined in a specific table in a database
• Scope of rows: (A) all columns having default values defined in tables accessible to the current user in Oracle database, (B) all columns having default values defined in tables in Oracle database
• Ordered by schema name, table name, column sequence number

Sample results

Here is a view of table columns' default values in Oracle SQL Developer: