Find out the default value for a column (Oracle)

Question

I wonder if there is a way to find out the default value of some column with a simple select statement. Tried several things like:

SELECT * FROM all_tab_columns WHERE table_name = 'tablename'

But I can't see the defaultvalues for the columns there. And no I do not want to use something like SQL Plus, I need a SELECT, guess there is some table providing that info?

Answer 1

Select TABLE_NAME, COLUMN_NAME, DATA_DEFAULT
from DBA_TAB_COLUMNS
where TABLE_NAME = 'TABLE_NAME';

Replace the Table_Name for which you want to see the default column data.

Answer 2

try the below query

Select * From USER_TAB_COLUMNS where TABLE_NAME ='Table Name'

Answer 3

Default values are in DATA_DEFAULT column from ALL_TAB_COLUMNS:

SELECT TABLE_NAME, COLUMN_NAME, DATA_DEFAULT 
  FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME = 'tablename'

[2021-01-26] Edit: Note that the 'tablename' parameter must match the case of real table name, as it appears on ALL_TAB_COLUMNS. More often it will be uppercase, so may be useful use UPPER() function to ensure it.

 WHERE TABLE_NAME = UPPER('tablename')