Oracle Database Discussions

Hi,

I am new to the Oracle database. And I need to archive data that is older than 60 days and delete the data from the tables. The tables have refertential integrity constraints. The archive data need to be resored back to the tables when necessary. I have read the previous messages posted by others and got to know that there are at least three ways to do it.

Firstly, I can use Oracle Partitioning. But this is only available in Enterprise edition. The version I will be using is Standard edition one. So I can't use the partitioning method.

Secondly, I can use Oracle Export utility with query option to export the data and delete the data from the tables after export.

Thirdly, I can create the same set of tables (historical tables) and write scripts to copy the data from the current tables to the historical tables, and delete the data in the current tables.

In my opinion, I think that the second method is move like an "archiving" as the Export will export the data into a file which can be stored in some sorts of storage devices.

The third method simply sotres the historical data in the database. I still need to back up those historical tables in case the database crashes.

If you know any other methods or any improvements to the above methods, please let me know. Thanks.