We have 11 tables for transactional data. Now, this data may have some data integrity issues. We have prepared a set of SQL scripts to fix the data integrity issues, which customer can execute from UI but some may need inputs from end-user.
In some cases, end-users comes back to us and asks us to revert the changes, because of functional impact, so we have 11 backup tables, which are same as transactional tables but without any constraints as we need to store corrupted data.
Question: My senior architect has proposed we change our design to use a single table for this purpose, and take backup in clob using dbms_xmlgen and persist in a CLOB/XMLtype. His main concern is the processing and maintenance overhead for these new tables, for example, an introduction to a new column to a transactional table will require the alteration in the backup table as well.
In my use case, 1. transactional table row count can be in millions, 2. at a time, we backup rows ranging from 1 to 100000 approximately. 3. Initially, we will be requiring to restore data in 5 % of cases, but eventually, it may go down nearer to zero. 4. Also, this is required in the initial time period, unless our scripts are proved to be error proof.