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?

  • 1
    How big is big? How much capacity have you got? What are you going to do with the new space? How much data % wise are going to be removed? Creating new tables means extra space usage and downtime. Doing logical deletes with views won’t save you any space. Regardless, having a delete proc that handles your foreign key orders should be appropriate - it will free space in the tables you delete from. Also, please upgrade from Oracle 11 Jun 22, 2022 at 7:02
  • About 20TB with a few TB left for growth (DB has been around for a long time). There are plans to upgrade, but that's a separate project. The idea with views is that the logical delete would be followed up by dropping the partition at a later date. As I mentioned above, one of the requirements is for there to be an undo mechanism. Jun 22, 2022 at 7:13
  • 1
    How long do you need to undo it for? Id be a bit cautious, sounds like they’re not really sure that this should be done. I would be costing storage expansions as an alternative. Thanks for clarifying the views method, yes that sounds alright, maybe use partition exchange to give you an extra period of undo. You could also just stick these partitions in tablespaces using cheaper storage Jun 22, 2022 at 7:28
  • That's unclear at this point, and there are conflicting interests at play. Thanks for the tip on partition exchange. I'll look into that some more. Jun 22, 2022 at 15:16
  • You want to increase disk free space. Will moving rows to a different table increase disk free space? Jun 22, 2022 at 19:39


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.