Wednesday, April 8, 2009

CTAS and Default Values

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.

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.


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. 


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.  

Popular Posts