procedure to delete several records from several tables

[crysu]

This is a text book case where you may want to consider deleting data by doing inserts instead of doing deletes.

Here is the scenario...
You want to delete 70%+ of the rows sitting on table_a
1- Rename table_a as table_b
2- Create table_a as select * from table_b where v_column_name > sysdate - v_retention_days
3- Build indexes
4- Gather fresh stats.
5- After business approval drop table_b

This is faster, generates less redo and ensures tables and indexes are not fragmented at the end of the process.

Can you afford a maintenance window to do it?

This will be the perfect case, but it's about a production database that can't be stopped or to interrupt activity to allow me a maintenance window.

I know that recreating the table with only the data that has to remain it is faster and more reliable but I'm afraid of losing rows.