loggis
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.
I will be doing this for oracle 9i and 10g databases.
I need a guide or outline to do this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.
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.
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.
Can you provide me with step by step guide on how to partition an existing huge table that contain around 11 years data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
then, for each table do something like this...
delete from yourtable where datecolumn < add_months(trunc(sysdate),