Problem solve Get help with specific problems with your technologies, process and projects.

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?
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.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.