can I quickly create tables for each partition?

Question

I have a table with two partitions and I would like to create two tables, one from each partition. Is there a quick way to do this (a la partition exchanges) instead of a create table as select (CTAS) statement?

For what it's worth, this need arose because I want to exchange these two partitions into another table; from what I've read, in order to do a partition exchange the data needs to be in a non-partitioned table.

Answer 1

If you don't need the original data, you can exchange the partitions with empty tables that you create yourself.

Demo:

SQL> create table originaltable
(
  id number primary key
)
partition by range (id)
(
  partition original1 values less than (10),
  partition original2 values less than (20),
  partition original3 values less than (MAXVALUE)
);

Table created.

SQL> insert into originaltable values (9);

1 row created.

SQL> insert into originaltable values (11);

1 row created.

SQL> insert into originaltable values (55);

1 row created.

SQL> create table newtable1_lessthan10
(
  id number primary key
); 

Table created.

SQL> ALTER TABLE originaltable
  EXCHANGE PARTITION original1
  WITH TABLE newtable1_lessthan10
  WITHOUT VALIDATION; 

Table altered.

SQL> select * from newtable1_lessthan10;

        ID
----------
         9

SQL>