Thanks for the question, Annajirao.
Asked: April 07, 2017 - 10:17 am UTC
Last updated: April 15, 2017 - 4:21 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi Tom, we have some exercise to delete records in > 300 tables depend on few requirements.
We are using dynamic sql delete statement iterating through tables list.
Is there any chance can I track what data is deleting from back-end, I want insert statements of the deleted data, so if I feel anything wrong happens then I will execute those statements to replace the data back again.
Thank u.
and Connor said...
You've got some options here you could explore
1) Simply backup the rows, ie, if you are dynamically generating the delete statements, then dynamically generate backup statements, eg, if you generate (say):
delete from T where col1 > 10;
then its trivial to augment that with:
create table tmp$t as select * from t where col1 > 10;
2) Triggers - add after-delete triggers to each table to write out, but that's *lots* more work than (1)
3) Use logminer to retrieve the UNDO_SQL for each delete.
4) Use flashback data archive to store history of the rows
But (1) look easiest to me...
Is this answer out of date? If it is, please let us know via a Comment