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.