All geek questions in one place

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

I have a table with huge data (say, millions of records, this is just an example from practice!) For 5 years with a breakdown for each year. Now I would like to save the latest data for 2 years and transfer the remaining data for 3 years to a new table called an archive?

What will be the Ideal method, with minimal downtime and high performance?

+4
source share
1 answer
alter table exchange partition

- the answer. This command selects a section of a section with a table segment. It is at the speed of light, because it performs only some reference relationships. So, you need temporary tables, because AFAIK you cannot exchange them directly.

Sort of:

 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;

Test your code before running.

+4
source

Source: https://habr.com/ru/post/1392881/

More articles:


All Articles