| PURGE DATA [message #154595] |
Mon, 09 January 2006 10:53  |
feroze
Messages: 144
Registered: September 2005
Location: chicago
|
Senior Member |
|
|
Hi All
Please tell me a better approach and some tips,
My case is :- I have 2 tables
MONTHLY_AC_DEPOSITS 22 million records
DAILY_AC_DEPOSITS 29 Million records
Requirement is to keep only last 4 months data in these tables and rest old data should be back up, no specific words just Back up.
What i did in Development is took a back up these tables , created another tables as back up and deleted more than 60% records from them.
Management wants now any autosys job or script so that every month end or every 15 days
it should be run and do the job of purging from main tables and at same time back up.
What you seniors suggest , what should be my approach,
Those 2 tables have 1 column as Account date,
Oracle is 9i Rel 2, o/s unix.
Some hints and steps are requested.
Thanks.
|
|
|
|
| Re: PURGE DATA [message #154722 is a reply to message #154595] |
Tue, 10 January 2006 05:26  |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
--> Create a "Before delete" trigger on the Main table, which stores the rows to another backup table before deletion from the main table.
--> create a procedure which deletes rows from the main table on the basis of date criteria.
--> Schedule the execution of above created procedure using DBMS_JOB package as per the requirement i.e.15 days or 1 month.
There could be other solutions also , let others also chip in.
regards,
tarun
[Updated on: Tue, 10 January 2006 05:27] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
