Swapping partitions
- From: Tony.Adolph@xxxxxx
- To: oracle-l@xxxxxxxxxxxxx, oracle-l-bounce@xxxxxxxxxxxxx
- Date: Thu, 2 Dec 2004 16:02:50 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
