• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

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.
0
loggis
Asked:
loggis
  • 5
  • 3
2 Solutions
 
sdstuberCommented:
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)
0
 
sdstuberCommented:
if deleting all of the old data will exceed your rollback/undo limits.  then do it in chunks

something like this....

LOOP
        DELETE FROM yourtable
              where datecolumn < add_months(trunc(sysdate),-36)
              and  ROWNUM <= 100000;   -- make this limit as large as your undo limits can handle
        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;
END LOOP;
0
 
loggisAuthor Commented:
Any guide or outline on how to archive the data in the tables?
I need to keep 3 years data on the production database.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
sdstuberCommented:
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.
0
 
loggisAuthor Commented:
Is partitioning the table an option i can use to achieve this task?
0
 
sdstuberCommented:
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.
0
 
loggisAuthor Commented:
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.
0
 
sdstuberCommented:
you can't simply add partitions to a table that was never partitioned to begin with.

create a new empty table with the partitions you want.

then copy the old data into the new table


since you want to purge your data anyway,  
archive the table,   then create the new partitioned table and only copy over the data you want to keep, then drop the original
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now