I have a very large Oracle database, with many many tables and millions of rows. I need to delete some of the rows end-to-end. By end-to-end, I mean, if I want to delete a row from a table then I must delete all the corresponding records in child tables first and ensure that deletion happens without any constraint violation errors while deleting the parent table entry.
As an example:
Table A defines dependency over Table B and Table C (means Table A has foreign key constraint on Table B and Table C). Table B defines dependency over Table C and Table D.
I want do delete rows in this specific order something like this:
Table A<=Table B<=Table C<=Table D
I have come up with two approaches:
Using a service API which takes parent table rowID and archives whole data in JSON format and keeps on deleting the entry using cascade option.
Other option is to use delete-with-cascade along with triggers to save the deleted values.
Once data is fetched, I want to better put it in similar archived tables Table_A_archived, Table_B_archived etc or If it could be fetched in file, that is fine too.
Any thoughts on initial approaches will be really valuable. If anyone has done this sort of thing before, it will be great to learn from their experience.
Just FYI, this is a live database and I can't do absurd things (I can obviously test it before pushing it to prod :)). Also, deletion/archival is not just on the basis of time.
Thanks in advance!