Search code examples
sqloracle-databasedatabase-partitioning

CREATE TABLE AS select * from partitioned table

I want to create a table using CTAS of partitioned table. New table must have all the data and partitions, subpartitions of old table. How to do this?

Solution

  • You need to first create the new table with all the partitions, there is no way you can add partition definitions to a CTAS. Once the table is created you can populate it using insert into .. select.

    You can use dbms_metadata.get_ddl to get the definition of the old table. 

    select dbms_metadata.get_ddl('TABLE', 'NAME_OF_EXISTING_TABLE')
from dual;

    Save the output of that into a script, do a search and replace to adjust the table name, then run the create table and then run the insert into ... select ...