如何从表分离分区并将其附加到oracle中的另一个分区? [英] How to detach a partition from a table and attach it to another in oracle?

问题描述

我有一张表，其中有5年的海量数据(例如，数百万条记录，这只是一个案例研究！)，每年都有一个分区.现在，我想保留过去2年的数据，并将3年数据的其余部分转移到称为存档的新表中?

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?

推荐答案

alter table exchange partition 

是答案.此命令将分区的段与表的段进行交换.由于它仅执行一些参考插入，因此它处于光速状态. 因此，您需要一些临时表，因为AFAIK不能直接交换它们.

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.

类似的东西:

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.

这篇关于如何从表分离分区并将其附加到oracle中的另一个分区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！