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@xxxxxx <tony.adolph@xxxxxx> 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 > > -- > //www.freelists.org/webpage/oracle-l > -- //www.freelists.org/webpage/oracle-l