1

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.

  • 1
    Do you need to keep the data in the original partitioned table? – Philᵀᴹ Dec 13 '13 at 13:43
  • I do not need to keep the data in the original table, though I would be curious for future needs if I could do that as well. – JHFB Dec 16 '13 at 19:40
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>
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.