Re: Swapping partitions

You can use the partition exchange clause if you are not using global indexes. If you are using global indexes you would need to rebuild them.

precreate empty exact matching table, with indexes.

alter table exchange partition partition_name with table table_name including indexes without validation;

You could then use the same command to exchange the resulting table into a partitioned archiving table.

Kenny

On Thu, 2004-12-02 at 10:02, 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

-- 
Kenny Payton
Software Architect
Public Records Group, Boca Raton
Choicepoint, Inc.
ken.payton_at_choicepointprg.net
o: (770)752-4054
c: (561)926-4119
--
http://www.freelists.org/webpage/oracle-l