Home » SQL & PL/SQL » SQL & PL/SQL » backing up data (merged 3)
backing up data (merged 3) [message #399138] Mon, 20 April 2009 21:05 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I have 2 tables
a_list and b_list_statis

The list_status has a date filed but a_list has no date field.

the common column between both the tables is the ID field.

now i need to write a process that will run weekly and keep only 30 days of data in a_list and move all data to a history table --a_history.

I am trying to follow the below process?is it fine

a_list contains 5.6 million data

step1

move data from a_list toa_bkp
(to create a_bkp will only 30 dys of data matching on the id field among both tables a_list and b_list_statis)

step2

move rest data to a_history(what is the best way to do so)insert?or any other way?

step 3

drop a_list

step4

rename a_history to a_list


this process is to run weekly?

please advice
Re: backing up data [message #399140 is a reply to message #399138] Mon, 20 April 2009 21:07 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Messages: 189
You choose to ignore Posting Guidelines.
We choose to not provide any advice.

[Updated on: Mon, 20 April 2009 21:10]

Report message to a moderator

Re: backing up data (merged 3) [message #399165 is a reply to message #399138] Tue, 21 April 2009 00:08 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps it is too complicated, as you'll have to:
- INSERT INTO a_bkp SELECT <the last 30 days> FROM a_list
- DELETE FROM a_list <the last 30 days>
- INSERT INTO a_history SELECT * FROM a_list
- DROP a_list
- RENAME a_history TO a_list
How about
- INSERT INTO a_history SELECT <records older than 30 days> FROM a_list;
- DELETE FROM a_list <records older than 30 days>


The whole process (whichever option you choose) can be put into a stored procedure that can be scheduled to run weekly by DBMS_JOB or DBMS_SCHEDULER (depending on your database version).
Re: backing up data (merged 3) [message #399377 is a reply to message #399165] Tue, 21 April 2009 21:42 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi

The technical assurance team had asked us to add a date field on the table a_list and make the table a range partition on week basis so that 4 weeks of data will be stored in a_list and every week when the job will run it will move one weekly partition data from a_list to the a_history table partition.

hence please advice how to use the partition here
as this is a new thing for me

Re: backing up data (merged 3) [message #399378 is a reply to message #399138] Tue, 21 April 2009 21:58 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+partition

[Updated on: Tue, 21 April 2009 22:33]

Report message to a moderator

Previous Topic: hierarchical data association with high volume table
Next Topic: restrict any user not to select more than 100 rows by his select
Goto Forum:
  


Current Time: Thu Dec 08 12:33:10 CST 2016

Total time taken to generate the page: 0.06407 seconds