Ask the Expert

Best method to archive data that may need to be accessed later

I'm not sure if you are the right person to ask but I could not find anybody better. So I'll try to describe my problem. We are developing a medical application that generates huge amounts of data. After some time we want to archive old data, but we still need to have the possibility to get to this data if necessary. I'd like to ask you for an idea of what solution would be good. Should we use a flag and then delete old data and put them to a new table in a read only tablespace and burn it on DVD? Or should we use a partitional table and simply detach the partition from the original table? Or should we use something completely different? We need to find a solution which has the lowest impact on production systems as on performance. Can you please help me to find something that would suit us?

    Requires Free Membership to View

Probably the "best" method is to first partition the data, hopefully on some sort of date column. After some time has past, all data in that partition can be archived. Create a new tablespace just for that partition and move that partition into that tablespace. Make the tablespace READ ONLY, and use Oracle's Transportable Tablespace feature to copy that partition off the database. You can then copy to tape or DVD or where ever. Once done, make the tablespace READ WRITE and drop the partition and the tablespace that you created. The data is now removed from your database. Should you need the data, simply plug in the tablespace you have copied to DVD and it is ready to go! A thorough testing of this procedure is in order.

This was first published in September 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: