CODEWITHSUNDEEP
sqloracle-databasedatabase-partitioning
Shriraj
Shriraj

Reputation: 153

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?

Upvotes: 1

Views: 6316

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions