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?

you need to first create the target table e.g. by using `dbms_metadata.get_ddl()` to get the original `create table` statement. Then do an `insert .. select ..` — , Oct 13 '15 at 06:51

Lalit Kumar B · Answer 1 · 2015-10-12T10:02:38.627

However noticed that CTAS does not copy the default value for each columns

The reason is plain and simple. CTAS is meant to only copy the data and not the metadata(except the table structure).

So, you will lose:

all constraints
all indexes
all grants

Basically, everything that is optionally related to the table.

You will have to pull the metadata and alter table and apply it explicitly.

alter table new_table_name modify (column default 1);

Alternatively, you could:

Use dbms_metadata.get_ddl to get the table DDL
Rename the table with new name
Insert data from old table to new table.

Of course, the latter would be much slower.

UPDATE

There is a way to provide the default value and not null constraint with CTAS:

Setup

SQL> create table t1 (id number default 1 not null, text varchar2(10));

Table created.

SQL> insert into t1 (text) values ('a');

1 row created.

CTAS

SQL> create table t2 (id default 1 not null, text )
  2  as select * from t1;

Table created.

SQL> select *from t2;

        ID TEXT
---------- ----------
         1 a

The new table is created with the default value along with the data from old table.

and you can take the all_tab_columns data: data_type, data_lenght, data_precision, data_scale, nullable to make it dynamic as well when creating the column properties string — przemo_pl, Oct 12 '15 at 10:21

score 0 · Accepted Answer · answered Oct 13 '15 at 06:43

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

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

2 Answers2