Swapping partitions

Hi all,
I have set up some partitioned tables partitioned by range on a date 
column.

The plan is to roll partitions, ie. each month create a new partition on 
the live table and archive the oldest partition.

I have written the code (perl / DBI) to create the tables and to "roll 
forward" - works a treat :-)

My question is how to roll the oldest partition off. 

I thought I could (after new partition is added)

1 - alter table <main_table> exchange partition <oldest_partition> with 
<temp_table>;

2 -  drop oldest_partition

3 - similar command to the above to "stick" the temp table onto a 
partition archive table as a new partition.

I could do something like:
insert into <arch_tab> select * from <temp_tab>;
truncate table <temp_tab>;

 but I thought there may be a funkier way using the exchange command!?  I 
base my thought on a line in Practical Oracle 8i by Lewis: "The best way 
to get bulk data >>into<< a partitioned table has to be the exchange 
partition cammand.",  but I must have missed that bit in his explanation. 
I can only see how to get it out.
 
Any ideas folks?

Cheers
Tony

PS Oracle 9.2.0.5

--
http://www.freelists.org/webpage/oracle-l

Other related posts: