Archiving Oracle Partitions

Hello Readers,

Let’s do some data archiving in Oracle. We often have a scenario when we have to archive the data stored in tables. The reasons could be several: you need to speed up data loading in the table, faster querying, you no longer need the data or may be you are done with the data ;).

This post will focus on archiving (or moving) Oracle partitions. This post assumes you have knowledge working with Oracle partitions.

Scenario: You have a SALES table that is partitioned by month. The partition nomenclature is SALES_YYMM. So SALES_1501 will hold data for Jan 2015, SALES_1603 will hold data for March 2016.

Requirement: Archive all 2015 data in SALES_ARCHIVE table.

Solution: The most optimal solution is to take all 12 partitions of 2015 from SALES table and move them into SALES_ARCHIVE table. Oracle does not provide an “archive partition” operation that will do the trick for us. But we can leverage the “exchange partition” operation. Which basically says exchange partition in SALES table with SALES_ARCHIVE table.

alter table SALES exchange partition SALES_1501 with table SALES_ARCHIVE

Above DDL will move data stored in Jan 2015 partition from SALES table into SALES_ARCHIVE. Similarly you can archive data for remaining 2015 months.

Job done? Well, not quite. Here is the caveat. Exchange partition operation only works with a partitioned and non-partitioned table. So SALES_ARCHIVE will have to be a non-partitioned table. So after you archive data for remaining months using above DDL, all of 2015 Sales data will be sitting in a non-partitioned table. Now if you want to restore data for Dec 2015, then you would have to do a DML (slow) operation instead of a DDL. If you are alright with this state, then you are job is done.

But chances are you would like to archive the data into a partitioned table, so that you can manage partitions in an efficient way. For doing that, you would have to move data from SALES (partitioned) table into an interim (non-partitioned) table and then from interim table into SALES_ARCHIVE (partitioned) table.

Below are the steps:

  1. Create an interim non-partitioned table, say SALES_INTERIM. An empty interim table can be created quickly using below. This is a one-time operation and will only hold data in transit from SALES table to SALES_ARCHIVE table.

create table SALES_INTERIM as select * from SALES where 1=2;

2. Move Jan 2015 data from SALES table to SALES_INTERM table.

alter table SALES exchange partition SALES_1501 with table SALES_INTERIM;

3. Create partition in SALES_ARCHIVE table to hold the data. Before you do this, you would have to create a partitioned SALES_ARCHIVE table with a dummy partition. Ideally the partition key in SALES_ARCHIVE should be same as SALES.

alter table SALES_ARCHIVE add partition SALES_1501 values ('partition_key');

4. Move data from SALES_INTERIM table to SALES_ARCHIVE.

alter table SALES_ARCHIVE exchange partition SALES_1501 with table SALES_INTERIM;


That’s it. These steps would archive data from SALES table to SALES_ARCHIVE.

Optionally, you could drop this partition from SALES table.

alter table SALES dorp partition SALES_1501;

Now if you wanted to restore data from SALES_ARCHIVE to SALES; you could just follow above steps by swapping table names. So steps would be:

  1. Move data from SALES_ARCHIVE to SALES_INTERIM.
  2. Create partition, if it does not exist, in SALES.
  3. Move data from SALES_INTERIM to SALES.

If you want to archive/restore a bunch of partitions, then do it sequentially. If you do in parallel, your SALES_INTERIM table could be in bad state.

If you want to read more about different partition operations, click here.

Hope this helps. Looking forward for any comments you may have.


Your’s Truly

About Badal Chowdhary

I am a Software Engineer by profession. I have done SCJP and SCWCD certifications. Like working on cutting edge technologies and frameworks. Driven by challenges and fascinated by technology. I love playing and watching sports: Cricket, Ping Pong, Tennis, Badminton, Racket Ball and Gym.
This entry was posted in Oracle and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s