Prem,
I
would re-visit the requirement. Why do you feel the need to delete the
data from the database? What is the purpose for this type of
requirement? It would be far easier to modify the requirement than to do
what you are thinking of doing.
Adding
columns to database tables indicating that a record has passed it's retention
policy and thus, is not included in queries, would be a much easier
solution.
Or,
simply moving these records to historical tables in the database - and NOT
deleting them from the system - is a much better solution. The data is
always accessible and not available in the current tables. And you will
not be playing the "get the data from tape and reload it" game with all of it's
problems (writing an offload program, table structure changes & offload
program versions).
Try
and keep this as simple as possible.
Hope
this helps
Tom Mercadante
Oracle Certified Professional
Oracle Certified Professional
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 06, 2002 4:13 AM
To: Multiple recipients of list ORACLE-L
Subject: Data Purging Strategy
Dear List,
I need some inputs from you all regarding purging data from the database.
This is the requirement
We define a retention period for all the data in the system.
When the retention period is reached, the data should be deleted, but then at a later time, some user might request for this purged data. So it must be possible to retrieve this data.
This is the strategy we have designed for this.
When the retention period is reached, move the data from the main database to an offline database. Then delete the data from the main database.
In the offline database, we cannot again keep it from long, so it has to moved to tapes. Now my question, how can we move this data to tapes and at the same time retrieve data from the tapes based on dates.
i.e, the user will ask for the data on a particular date, so it must be possible to retrieve data from the tapes based on a date and load it to the database tables.
Regards
Prem
