CTAS will loss default value define on columns

This is about the CTAS (Create Table As Select...) operations during the creation of partitioned tables from normal tables in an Oracle database and createion no_partition tables same as that.

The CTAS operation although copies column constraints such as NULL, NOT NULL from the normal table to the partitioned table during the partitioned table creation, it does not copy DEFAULT VALUEs of the columns.

anbob@DEVDB>l
  1  SELECT COLUMN_NAME,DATA_TYPE,DATA_DEFAULT FROM dba_TAB_COLUMNS WHERE TABLE_NAME='ICME_PROJECT_SCORE' and owner='ICME6'
  2*
anbob@DEVDB>/
COLUMN_NAME                    DATA_TYPE                      DATA_DEFAULT
------------------------------ ------------------------------ ------------------------------
SCORE_ID                       NUMBER
IC_CODE                        VARCHAR2
SCORE_ACTIVITY_DATE            DATE
SCORE_REMARK                   VARCHAR2
SUBJECT_ID                     NUMBER
GIVE_ORG_ID                    NUMBER
FROM_ORG_ID                    NUMBER
SCORE_CHECK_FLAG               NUMBER
ORG_ID                         NUMBER
ADMIN_ID                       NUMBER
CREATE_TIME                    DATE                           SYSDATE
UPDATE_TIME                    DATE                           SYSDATE
IS_VALID                       NUMBER                         0
TO_ORG_ID                      NUMBER
REPEAT_FLAG                    NUMBER                         0
SOURCE_FLAG                    NUMBER                         1
anbob@DEVDB>create table parttab 
  2  partition by hash(score_id) partitions 8 as select * from icme6.icme_project_score where rownum<10;
Table created.

Tip:
Create the partitioned table with CTAS from the normal table above, consider using NOLOGGING or PARALLEL table creation option to avoid trashing the logs if you think this data is recoverable from elsewhere. This will also create the table faster.

anbob@DEVDB>select table_name,partition_name from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
PARTTAB                        SYS_P46
PARTTAB                        SYS_P44
PARTTAB                        SYS_P42
PARTTAB                        SYS_P43
PARTTAB                        SYS_P41
PARTTAB                        SYS_P45
PARTTAB                        SYS_P47
PARTTAB                        SYS_P48
anbob@DEVDB>SELECT COLUMN_NAME,DATA_TYPE,DATA_DEFAULT FROM dba_TAB_COLUMNS WHERE TABLE_NAME='PARTTAB' and owner='ANBOB';
COLUMN_NAME                    DATA_TYPE                      DATA_DEFAULT
------------------------------ ------------------------------ ------------------------------
SCORE_ID                       NUMBER
IC_CODE                        VARCHAR2
SCORE_ACTIVITY_DATE            DATE
SCORE_REMARK                   VARCHAR2
SUBJECT_ID                     NUMBER
GIVE_ORG_ID                    NUMBER
FROM_ORG_ID                    NUMBER
SCORE_CHECK_FLAG               NUMBER
ORG_ID                         NUMBER
ADMIN_ID                       NUMBER
CREATE_TIME                    DATE
UPDATE_TIME                    DATE
IS_VALID                       NUMBER
TO_ORG_ID                      NUMBER
REPEAT_FLAG                    NUMBER
SOURCE_FLAG                    NUMBER

anbob@DEVDB>create table testpart as select * from  icme6.ICME_PROJECT_SCORE where rownum<=20;
anbob@DEVDB>SELECT COLUMN_NAME,DATA_TYPE,DATA_DEFAULT FROM dba_TAB_COLUMNS WHERE TABLE_NAME='TESTPART' and owner='ANBOB';

COLUMN_NAME                    DATA_TYPE                      DATA_DEFAULT
------------------------------ ------------------------------ ------------------------------
SCORE_ID                       NUMBER
IC_CODE                        VARCHAR2
SCORE_ACTIVITY_DATE            DATE
SCORE_REMARK                   VARCHAR2
SUBJECT_ID                     NUMBER
GIVE_ORG_ID                    NUMBER
FROM_ORG_ID                    NUMBER
SCORE_CHECK_FLAG               NUMBER
ORG_ID                         NUMBER
ADMIN_ID                       NUMBER
CREATE_TIME                    DATE
UPDATE_TIME                    DATE
IS_VALID                       NUMBER
TO_ORG_ID                      NUMBER
REPEAT_FLAG                    NUMBER
SOURCE_FLAG                    NUMBER

To fix this problem, you will have to either explicitly change your code to pass in a date value for the hava default value columns, or alter the table after CTAS and modify the column to have a default value.

anbob@DEVDB>create table parttab_2(score_id,REPEAT_FLAG default 0)
 partition by hash(score_id) partitions 8 
 as select score_id,REPEAT_FLAG from icme6.icme_project_score where rownum<10;
Table created.
anbob@DEVDB>SELECT COLUMN_NAME,DATA_TYPE,DATA_DEFAULT FROM dba_TAB_COLUMNS WHERE TABLE_NAME='PARTTAB_2' and owner='ANBOB'
COLUMN_NAME                    DATA_TYPE                      DATA_DEFAULT
------------------------------ ------------------------------ ------------------------------
SCORE_ID                       NUMBER
REPEAT_FLAG                    NUMBER                         0

or

anbob@DEVDB>alter table parttab modify REPEAT_FLAG default 0;
Table altered.