In my previous blog entry I introduced the NOCASCADE mode. Thus you probably guessed there would be a CASCADE mode as well. And you would be correct. I won’t show all of the SQL for the test case because that is available in the CASCADE blog entry. I’ll just highlight the change and show the results here.
SQL> DECLARE 2 v_names SYS.TXNAME_ARRAY := SYS.TXNAME_ARRAY('INSERT'); 3 BEGIN 4 DBMS_FLASHBACK.TRANSACTION_BACKOUT( numtxns => 1, 5 names => v_names, 6 scnhint => hold_stuff.v_scn, 7 options => DBMS_FLASHBACK.CASCADE ); 8 END; 9 / PL/SQL procedure successfully completed. SQL> SELECT demo_name cascade 2 FROM demo; no rows selected
Specifying CASCADE mode allowed the initial INSERT transaction to be backed out. But not in the way you might think. Here are the SQL statements used to back it out.
update DRH.DEMO set DEMO_NAME = 'Demo 1' where DEMO_ID = '1' and DEMO_NAME = 'Demo One' delete from DRH.DEMO where DEMO_ID = '1' and DEMO_NAME = 'Demo 1'
The backout function worked backwards from the current state of the record (identified by the primary key – DEMO_ID) and backed out every transaction all the way back to the initial INSERT. The first backout SQL updated the DEMO_NAME back to ‘DEMO 1’ to ensure the final backout would find the record in the same state it was when the transaction originally occurred.
So be careful when doing CASCADE backouts because if the desired transaction is far back in the past the backout may take some time.
Thanks for reading.