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.
loggisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.