Reorganizing Table using CTAS in Oracle

Steps:-

•   Create a copy of the original table using CTAS.

           SQL> create table new_table as select * from old_table;

•      Drop the original table and all indexes/constraints

           SQL> drop table emp cascade constraints;

•      Rename new table

          SQL>  alter table new_emp rename to old table name;

•      Recreate all indexes because row-ids are change when you move the table.

         
Advantages:-

1. Easy to use.
2. With small maintenance window this CTAS option is very helpful for reorganizing table online.
2. Table is available for DML operations during CTAS (but these DMLs are not copied to target table).


Drawbacks:-

1. CTAS is slow.
2. Additional space is required.
3. Any DML that is performed on the table during CTAS operation is not recorded in target table.
    Differential data should be copied using insert into..select with required filters.
4. Indexes  need to be manually created on target table.


Thanks Reading....