Transaction Backout Introduction

Oracle’s TRANSACTION_BACKOUT function does just what it’s name implies – it backs out committed transactions. Here is an example:

-- Create a simple demo table
SQL> CREATE TABLE demo
 2 ( demo_id NUMBER NOT NULL PRIMARY KEY,
 3   demo_name VARCHAR2(30) NOT NULL UNIQUE );

Table created.

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

PL/SQL procedure successfully completed.

-- Create package to hold the SCN value later
SQL> CREATE OR REPLACE PACKAGE hold_stuff AS
 2     v_scn NUMBER;
 3   END;
 4 /

Package created.

-- Save the current SCN into the package value
SQL> BEGIN
 2     hold_stuff.v_scn := 
 3        DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
 4 END;
 5 /

PL/SQL procedure successfully completed.

-- Set the name of the transaction for later backout
SQL> SET TRANSACTION NAME 'UPDATE';

Transaction set.

-- Update the record in the table and commit
SQL> UPDATE demo
 2   SET demo_name = 'Demo One';

1 row updated.

SQL> COMMIT;

Commit complete.

-- Verify the demo name has been changed
SQL> SELECT demo_name
 2     FROM demo;

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

-- Now backout the update transaction using the
-- transaction name and SN value
SQL> DECLARE
 2     v_names SYS.TXNAME_ARRAY :=
 3        SYS.TXNAME_ARRAY('UPDATE');
 4 BEGIN
 5   DBMS_FLASHBACK.TRANSACTION_BACKOUT( numtxns => 1,
 6                     names => v_names,
 7                     scnhint => hold_stuff.v_scn );
 8 END;
 9 /

PL/SQL procedure successfully completed.

-- Verify the demo name was changed
SQL> SELECT demo_name
 2 FROM demo;

DEMO_NAME
------------------------------
Demo 1

This is just an introduction. In the next few blog entries I’ll explain the functionality in much more detail.

Thanks for reading.