When backing out transactions from the past using undo SQL statement it is important to consider the current data – especially if the undo SQL violates database constraints.
Consider this example scenario that inserts 2 records in one transaction and then deletes them both in a second transaction. Then in a third transaction it re-inserts the first row.
What will happen when we try to backout the deletion (second transaction)?
-- insert 2 records SET TRANSACTION NAME 'INSERT'; INSERT INTO demo VALUES(1,'Demo 1'); INSERT INTO demo VALUES(2,'Demo 2'); COMMIT; -- delete both records -- this is the transaction to be backed out later SET TRANSACTION NAME 'DELETE'; DELETE demo; COMMIT; -- insert the first record again SET TRANSACTION NAME 'INSERT2'; INSERT INTO demo VALUES(1,'Demo 1'); COMMIT;
Will the backout fail when it tries to re-insert the row with DEMO_ID=1 because it violates the primary key constraint. Will it throw this error message?
ERROR at line 1: ORA-55511: Flashback Transaction experienced error in executing undo SQL ORA-00001: unique constraint (ORA-00001: unique constraint (DRH.SYS_C0010742) violated.) violated ORA-06512: at "SYS.DBMS_FLASHBACK", line 51 ORA-06512: at "SYS.DBMS_FLASHBACK", line 86 ORA-06512: at line 4
It’s interesting to note the extra “violated” at the end of the message.
Is the error message thrown by all 4 modes of backout transaction? How many rows will be in the table afterwards? I’ll give you a hint – its not always 1 row.
DBMS_FLASHBACK.NOCASCADE = error message? 1 row? DBMS_FLASHBACK.NOCASCADE_FORCE = error message? 1 row? DBMS_FLASHBACK.NONCONFLICT_ONLY = error message? 1 row? DBMS_FLASHBACK.CASCADE = error message? 1 row?
In my next blog post I’ll explain the answer.
Thanks for reading.