how to get coulmns with DEFAULT value in table using GETDDL in ORACLE

Question

i wrote an automated script using dbms_metadata to generate CTAS scripts for all the tables in schema.

SELECT ' CREATE TABLE '
  ||SUBSTR(TABLE_NAME,1,26)
  ||'_BKK ('
  ||COL
  ||')'
  || ' NOLOGGING AS SELECT '
  || COL
  || ' FROM '
  || TABLE_NAME
  ||';' QUERY
FROM DATA2;

However noticed that CTAS does not copy the default value for each columns. is there any way i can get the default columns and value so that i can create automated script around it?

Answer 1

Thanks @Lalit Kumar B for the detailed description of how CTAS works. However i wanted to get coulmns with DEFAULT value in table so that i can write some automation script on it.

Select TABLE_NAME, COLUMN_NAME, DATA_DEFAULT
from DBA_TAB_COLUMNS
where DATA_DEFAULT is not null

The above query helped getting all the columns with DEFAULT and using this i appended to the result of the automated script