How to detach a partition from a table and attach it to another in oracle?

Question

I have a table with huge data( say millions of records, its just a case study though!) of 5 years, with a partition for each year. Now i would want to retain the last 2 years data, and transfer the rest of the 3 year data to a new table called archive?

What would be the Ideal method, with minimal down time and high performance?

Solution

alter table exchange partition 

is the answer. This command exange the segment of a partition with the segment of a table. It is at light speed because it does only some reference interchages. So, you need some temp tables, because AFAIK you can't exchange them directly.

Something like:

create table tmp_table(same columns);
Add partition p_2011 in table ARCH_TABLE;

ALTER TABLE CURR_TABLE EXCHANGE PARTITION P_2011 WITH TABLE tmp_table;
ALTER TABLE ARCH_TABLE EXCHANGE PARTITION P_2011 WITH TABLE tmp_table;

Please test test your code before run.

Comments

[azzaxp]

thanks for the the Response, I would want to know if the destination table arch is necessary to have the partition.

[Florin Ghita]

Yes is absolutely necessary. It is the second line in the example(add partition). And I recommend you that the tables should have same partitions, whith same ranges.

[azzaxp]

The destination Table does not necessarily have the Partition.

[Florin Ghita]

So, please add it. The exchange command won't work without it. Alter table add partiton is the command.