Re: Swapping partitions

use the same exchange partition command with the new table and the temp table that is holding data and u will have the Temp Table becomming empty and the new Partiton poulated with data.

Also Check JL's Website there is a good discussion there on What he thinks abt exchanging partitions.

Cheers!
Ganesh

On Thu, 2 Dec 2004 16:02:50 +0100, tony.adolph_at_o2.com <tony.adolph_at_o2.com> wrote:
> 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
>

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