Please let me know which approach you would take.
The problem
I have a large Oracle 11g DB that needs to have old records purged to free disk space. One of the requirements for the project is that we be able to restore records after they have been removed from the main DB (and are thus no longer visible to the application).
IDs from the main transaction table will selected for deletion by applying business rules *, while records in other tables will be removed based on foreign key relations to the main table. Other tables' records will be removed based on relations to those tables, and so on.
There are foreign key constraints in place, but they are mostly set to RESTRICT instead of CASCADE, meaning we would need to work backwards when doing deletes.
* Note that the business rules here are more complex than just "delete all records older than X"
The approach being considered
First, create a copy of each affected table (which is most tables in the DB) with something like:
CREATE TABLE foo_archive AS SELECT 1 AS archive_batch_id, foo.* FROM foo WHERE 1 = 0;
...duplicating all columns and adding an "archive_batch_id" column that could be used as the key for table partitioning.
Then, a script would run to move the data to be archived from the original tables to the archive tables using something like:
INSERT INTO foo_archive SELECT 1234, foo.* FROM foo WHERE foo_parent_id IN (1, 2, 3);
DELETE FROM foo WHERE foo_parent_id IN (1, 2, 3);
...where "1234" is a unique batch ID that would be generated each time the script is run. That way if we identify records that shouldn't have been deleted, they could be restored by reversing the scripted process.
Thoughts on using views
It had occurred to me that if these tables were replaced with views, and the underlying base table had a field (like archive_batch_id above) that determined row visibility, and could be used as a key for partitioning...that could remove the need to move records around like I've described.
I'm very nervous about that approach, however, since it would mean replacing nearly every table in the DB with a view. What would the performance implications of that be?
The potential downtime from trying to set that up on a very large DB is also a concern.
What other gotchas might I encounter with this sort of approach?
