NOCASCADE_FORCE Mode

Last but definitely not least is the NOCASCADE_FORCE mode. It implements the following rule when backing out transactions.

Backout the desired transaction on an as-is, where-is basis.

Here’s an example:

-- save the current SCN
SQL> BEGIN
 2     hold_stuff.v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
 3   END;
 4 /

PL/SQL procedure successfully completed.

-- Perform a single insert in a transaction
SQL> SET TRANSACTION NAME 'INSERT';

Transaction set.

 SQL> BEGIN
 2      INSERT INTO demo
 3      VALUES(1,'Demo 1');
 4      COMMIT;
 5    END;
 6 /

PL/SQL procedure successfully completed.

-- Update the inserted record in another transaction
SQL> SET TRANSACTION NAME 'UPDATE';

Transaction set.

SQL> UPDATE demo
 2   SET demo_name = 'Demo One';

1 row updated.

SQL> COMMIT;

Commit complete.

-- Verify the record
SQL> SELECT demo_name
 2     FROM demo;

DEMO_NAME
------------------------------
Demo One

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.NOCASCADE_FORCE );
 8   END;
 9 /

PL/SQL procedure successfully completed.

-- Check the record
SQL> SELECT demo_name
 2    FROM demo;

DEMO_NAME
------------------------------
Demo One

1 row selected.

Along the way the backout process assembled the following SQL to potentially apply.

delete from DRH.DEMO where DEMO_ID = '1' and DEMO_NAME = 'Demo 1'
update DRH.DEMO set DEMO_NAME = 'Demo 1' where DEMO_ID = '1' and DEMO_NAME = 'Demo One'

But it only executed this one:

delete from DRH.DEMO where DEMO_ID = '1' and DEMO_NAME = 'Demo 1'

But that record (DEMO_ID=1) no longer existed in that state (DEMO_NAME=’Demo 1′) so nothing was deleted.

Thanks for reading.