Move Partitions between Tables

You might wonder why you should move a partition from one table to another but actually there are several reasons. The following one came up in a session I had with a customer who wants to archive “old” data. The actual data is partitioned (INTERVAL) and initially his idea was to move the rows to the archive table with INSERT AS SELECT. The archive table has to stay for a minimum of 10 years but is very seldom used. As there are global indexes on the current table with about 300 million rows we couldn’t simply keep the old data in the current table.

But instead of using a non-partitioned table for the archived data I told him to create a table which has the same layout as the current table because

• after the 10 years he can drop the oldest partition
• he can exchange partitions instead of moving data around

So here’s the outcome of our discussion:

1 Environment

The starting point is two tables with identical definition:

1. Table ACTIVE: An interval partitioned table with the current data since January 1^st 2013. Each year a new partition is created automatically.
2. Table ARCHIVE: A range partitioned table with “old” data until December 31^st 2012. Every year a new partition has to be created manually.

1.1 Example

1.1.1 Table ACTIVE

CREATE TABLE active
(
   id NUMBER CONSTRAINT pk_active PRIMARY KEY,
   myvalue VARCHAR2(20 CHAR),
   mydate DATE
)
PARTITION BY RANGE (mydate)
INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
(
   PARTITION PDUMMY VALUES LESS THAN 
                    (TO_DATE('01.01.2013','DD.MM.YYYY')),
   PARTITION P2012 VALUES LESS THAN 
                    (TO_DATE('01.01.2013','DD.MM.YYYY')),
   PARTITION P2013 VALUES LESS THAN 
                    (TO_DATE('01.01.2014','DD.MM.YYYY')),
   PARTITION P2014 VALUES LESS THAN 
                    (TO_DATE('01.01.2015','DD.MM.YYYY')),
   PARTITION P2015 VALUES LESS THAN 
                    (TO_DATE('01.01.2016','DD.MM.YYYY'))
);

1.1.2 Table ARCHIVE

CREATE TABLE archive
(
   id NUMBER CONSTRAINT pk_archive PRIMARY KEY,
   myvalue VARCHAR2(20 CHAR),
   mydate DATE
)
PARTITION BY RANGE (mydate)
(
   PARTITION POLD VALUES LESS THAN 
                    (TO_DATE('01.01.2010','DD.MM.YYYY')),
   PARTITION P2010 VALUES LESS THAN 
                    (TO_DATE('01.01.2011','DD.MM.YYYY')),
   PARTITION P2011 VALUES LESS THAN 
                    (TO_DATE('01.01.2012','DD.MM.YYYY'))
);

2 Preparation

With exchange partition a table will be exchanged with a partition. This implies that both the table and the partition have the same definition. Unfortunately there is no way to exchange a partition from one table with a partition of another table. To work around this restriction we need to use an interim table instead.

2.1 Table INTERIM

The unpartitioned table INTERIM must be created with the same layout as the table ACTIVE. This table will stay empty except for the duration of the migration.

CREATE TABLE interim
(
   id NUMBER CONSTRAINT pk_interim PRIMARY KEY,
   myvalue VARCHAR2(20 CHAR),
   mydate DATE
);

3 Exchange Partition

3.1 Empty Partition in ARCHIVE

First an empty partition has to be created in the ARCHIVE table.

ALTER TABLE archive
   ADD PARTITION P2012 VALUES LESS THAN
                    (TO_DATE('01.01.2013','DD.MM.YYYY'));

BTW: If we create the table as interval partitioned (like the table ACTIVE) this command will fail as it isn’t allowed to add a partition manually to an interval partitioned table instead a new partition will be created automatically if data within the new interval is inserted. The work around will be to simply add one row to the table which creates the required partition:

INSERT INTO archive VALUES 
            (99999999,'dummy',to_date('31.12.2012','DD.MM.YYYY'));
ROLLBACK;

In this case the partition will be created with a system defined name (e.g. SYS_P64) but without any content.

3.2 Partition Exchange part 1

Now the INTERIM table will be exchanged with the Partition P2012 of the ACTIVE table:

ALTER TABLE active EXCHANGE PARTITION p2012 WITH TABLE interim;

Now the Partition p2012 in the table ACTIVE is empty and the table INTERIM has some rows:

SELECT count(*) FROM active PARTITION (P2012)

NO ROWS SELECTED

SELECT count(*) FROM interim

COUNT(*)
=============
xxxxxxxxxxxxx

3.3 Partition Exchange part 2

In the next step the INTERIM table will be exchanged with the Partition P2012 of the ARCHIVE table:

ALTER TABLE archive EXCHANGE PARTITION p2012 WITH TABLE interim;

As a result the table INTERIM should be empty again and the partition P2012 of the table ARCHIVE should hold the content of the year 2012.

SELECT count(*) FROM active PARTITION (P2012)

NO ROWS SELECTED

SELECT count(*) FROM interim

NO ROWS SELECTED

SELECT count(*) FROM archive PARTITION (P2012)

COUNT(*)
=============
Xxxxxxxxxxxxx

That’s it! We are done with the migration of the table data within seconds because no data has been moved but only the data dictionary information has been changed.

3.4 Cleanup

Now the partition P2012 in the table ACTIVE can be dropped.

ALTER TABLE aktiv DROP PARTITION p2012;

3.5 Index Rebuild

If there were global indexes on the tables they have to be rebuild as they became UNUSABLE during the exchange.

ALTER INDEX idx… REBUILD;

4 Conclusion

Exchange partition is a real great tool to move data between different tables without the need for downtime or huge batch processing.

The only risk for this process is the validation if the partition meets the requirement of the archived table during the exchange partition. As to the logic of this scenario the partition to be exchanged will probably the oldest one in the actual table but the newest one in the archive table. If you exchange the oldest partition of your table there is a risk that it includes data which is older than expected. In this case P2012 of table ACTIVE might hold data from 2011 or even 2010. The exchange partition to the table ARCHIVE will fail as the partition criteria is not met because in ARCHIVE there is a partition P2011 holding data from 2011 and a partition P2010 with data from 2010. To avoid this error it might be a good idea to add a dummy partition to the table ARCHIVE for accidentally added old data.