Link to home
Start Free TrialLog in
Avatar of loggis
loggisFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Data archiving in oracle 9i and 10g databases

The data are on different tables without partition. After successful archiving, some years data will be removed from the production database to reduce space usage. At the end of the i want to leave about 3 years data on the live databases.
I will be doing this for oracle 9i and 10g databases.
I need a guide or outline to do this.
Avatar of Sean Stuber
Sean Stuber

assuming you have a date column to identify your old data....

then, for each table do something like this...


delete from yourtable where datecolumn < add_months(trunc(sysdate),-36)
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of loggis

ASKER

Any guide or outline on how to archive the data in the tables?
I need to keep 3 years data on the production database.
iterate in chunks of a month/week/year (some reasonable amount of data),  


export the data with where clauses to pull the older data, archive it, then purge it, then move to the next.


or,  even simpler  - archive all of it.  Backup/export the entire db as is,  including the recent data.  Then purge.
Avatar of loggis

ASKER

Is partitioning the table an option i can use to achieve this task?
if you have a license for partitioning, (you do have to pay for it separately than the rest of oracle)

then it might make it somewhat easier going forward, but not really for the first time, because first you'd have to do all of the partitioning, then the purge.

once your  data is partitioned, say, by month,  you can simply drop the partition after archiving.  Then if you have any global indexes, rebuild them.
Avatar of loggis

ASKER

Thank you for the quick response. Our license cover this.
Can you provide me with step by step guide on how to partition an existing huge table that contain around 11 years data.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial