Recently I created a table as select from the table and then I found the default values are no more in the new table. Found the below from metalink , it should be a usefull information for those who use CTAS mostly.
579636.1 Create Table As Select Does Not Copy Table's Default Values.
Note: Please refer to the latest information on the metalink document.
Symptoms
If you use CTAS ( Create Table As Select ) to copy a table, the new duplicate table does not contain the default values of the original table. As a result, if you use DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, it does not copy column's default values.
Cause
There is an unpublished ER ( Enhancement Request ) # 4341693 suggesting that CTAS should inherit the table default values and that should enable DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS to copy the column's default values which is handled in another ER # 7596038.
Solution
To implement the solution, please execute the following steps:
1. Do not use CTAS to copy the table, use another method.
2. If you want the column's default values to be copied when using DBMS_REDEFINITION, the table has to be pre-created with the default column values before getting re-organized with DBMS_REDEFINITION.
