Home » SQL & PL/SQL » SQL & PL/SQL » How to move data between two partitions? (Oracle 10g,,Solaris)
How to move data between two partitions? [message #282813] Fri, 23 November 2007 13:53 Go to next message
Messages: 19
Registered: November 2007
Junior Member
Hi All,

I need help on this.I have month by month range partitioned table.Every month data will goee to default partition before loading into default old data need to move to previous month partition.

My partitions like :
LN_NOV_2007--Current month
LN_OCT_2007--Previous month

I need command to move data between two partitions.

Re: How to move data between two partitions? [message #282827 is a reply to message #282813] Fri, 23 November 2007 22:53 Go to previous message
Messages: 1834
Registered: November 2006
Senior Member


old data need to move to previous month partition

Are you aware of the Partitioned Tables and Indexes Concepts?

Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of one or more columns that determines the partition for each row.

"Moving" data comes into play when partition key is UPDATEd. According to SQL Reference it shall be possible when you enable row movement. However I have never used it and do not think it is appropriate for 99.9% cases.

In my opinion you shall re-think the scenario. I suppose you have RANGE partitions on date column (not stated in your post). There are two possibilities:
1. Immediately after first load, SPLIT the LN_DEC_9999 partition (eg. to LN_DEC_2007 and LN_DEC_9999), so the new data will be in the first partition. I do not recommend this as it may take too much time.
2. Get rid of LN_DEC_9999 partition. In 11g, you can use interval partitioning, otherwise you have to create the partition(s) for new data yourself. DROP the old partitions when data is not needed, or MERGE them when you want to put them together.
Consult SQL Reference for the correct syntax.
Previous Topic: UPPER CASE update
Next Topic: diffrence between both query
Goto Forum:

Current Time: Tue Jan 24 05:40:52 CST 2017

Total time taken to generate the page: 0.06297 seconds