Friday, December 4, 2009

CTAS and column default values

Affecting 8i up to 10gR2

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.

To implement the solution, please execute the following steps:

1) Do not use CTAS to copy the table, use another method.

2) Use CTAS and then modify columns giving them their default values where necessary.

3) 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.

No comments:

Post a Comment