Partition exchange loading for specific subpartitions

Looking at an archive strategy - where we have to archive a specific dataset.

Rather than a insert/delete routine - I was thinking of using partition exchange.

The to-be archived table is interval range partitioned on date, with a list subpartition on country.

It is for specific countries that I want to partition exchange.

create table 
test_table
(tbl_id number,
country varchar2(2),
sales_dt date,
volume number)
partition by range (sales_dt) interval (NUMTOYMINTERVAL(1,'Month'))
subpartition by list (country)
Subpartition template
(subpartition p_ireland values ('IR'),
subpartition p_france values ('FR'),
subpartition p_other values (DEFAULT))
(partition before_2008 values less than (to_date('01-JAN-2008','DD-MON-YYYY')));

The data loaded falls into the partitions and subpartitions correctly. All the partitions names are system generated.

When I come to partition exchange for all the 'FR' subpartitions- I can't determine the logic.

Using

Alter table test_table
exchange subpartition system_generated_name
with table TEST_TABLE_ARCH;

I can swap out a specific 'known' subpartition.

I know 开发者_开发技巧you can use the 'for' logic with Oracle 11g but can't get the syntax to work.

Any ideas?

---

We tested this in the past and believed that we had to come up with a procedure to do it right. The goal is to do two partition swaps: one between the source table and your empty swap table, and a second one between your now-populated swap table and the archive table.

Prereqs: Create an empty swap table to do the partition swap with your source table. Also, create an archive table that will be partitioned as well.

High-level process:

• create new empty partition in the archive table.
• Do partition exchange between swap table and source partition you want to archive (result: empty partition in source table, archived partition in swap table)
• Do partition exchange between swap table and new empty partition in the archive table; (result: original source table partition is now a partition in your archive table and swap table is back to being empty)
• Drop empty partition in your source table (assuming you don't want to reuse it)

We never coded this so this process may need tweaking but we think this is what we would have had to do if we pursued such a strategy.