09/02/2016
Carefully use CTAS while using default column values.
snippet below..
SQL> create table test
2 (name varchar2(20),
3 birthdate date default sysdate);
Table created.
SQL> insert into test(name) values('Rambo');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
NAME BIRTHDATE
-------------------- ---------
RAMBO 09-FEB-16
SQL> create table test1 as select * from test;
Table created.
SQL> select * from test1;
NAME BIRTHDATE
-------------------- ---------
RAMBO 09-FEB-16
SQL> insert into test1(name) values ('JOG');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1;
NAME BIRTHDATE
-------------------- ---------
RAMBO 09-FEB-16
JOG ---> date default not came
SQL> set long 10000
SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;
DBMS_METADATA.GET_DDL('TABLE','TEST')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."TEST1"
( "NAME" VARCHAR2(20),
"BIRTHDATE" DATE DEFAULT sysdate
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
SQL> select dbms_metadata.get_ddl('TABLE','TEST1') from dual;
DBMS_METADATA.GET_DDL('TABLE','TEST1')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."TEST1"
( "NAME" VARCHAR2(20),
"BIRTHDATE" DATE ----> Missing default
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
Note: Default option did not copied above.
