Home » SQL & PL/SQL » SQL & PL/SQL » exchange partition for moving data into history table (oracle 10g)
exchange partition for moving data into history table [message #431078] Sat, 14 November 2009 08:02 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi All

I have a table temp_output

This table is monthly partitioned on entry_date.

I need to build a proces that will run monthly and will move data from temp_output to temp_output_history.

The table temp_output should contain 1-2 months of data.

and rest all should be moved to history table.

I think exchange partition will be the option for this.

Can anyone hint me a script that i can refer to build my requirement.

Thanks
Re: exchange partition for moving data into history table [message #431079 is a reply to message #431078] Sat, 14 November 2009 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>temp_output to temp_output_history.
post DDL for both tables.

First make it work, then it fancy.

post working SQL that accomplishes goal for one hardcoded month
with working test case showing before and after results

[Updated on: Sat, 14 November 2009 09:58]

Report message to a moderator

Re: exchange partition for moving data into history table [message #431310 is a reply to message #431078] Mon, 16 November 2009 11:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The best solution depends a lot on the amount of data that you're moving.
If it's just a few 10s or 100s of thousand rows, for a monthly process I'd probably just INSERT and DELETE.
Previous Topic: Creating a procedure to close cursors
Next Topic: Pull out the values which is there in Listing but not there in Table
Goto Forum:
  


Current Time: Fri Dec 09 09:34:40 CST 2016

Total time taken to generate the page: 0.16139 seconds