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.