Quiz: Backout And Constraints

When backing out transactions from the past using undo SQL statement it is important to consider the current data – especially if the undo SQL violates database constraints. Consider this example scenario that inserts 2 records in one transaction and then deletes them both in a second transaction. Then in a third transaction it re-inserts … Read more

Viewing Backout SQL

The SQL generated for a backout transaction is available in the USER_FLASHBACK_TXN_REPORT. It’s in XML format but here is a query I concocted to extract it. SQL> SELECT REPLACE(EXTRACTVALUE(column_value,’/USQL’),CHR(9),”) oh_yeah 2 FROM ( SELECT column_value 3 FROM TABLE( SELECT XMLSEQUENCE(XMLTYPE(xid_report).EXTRACT(‘/COMP_XID_REPORT/TRANSACTION/UNDO_SQL/USQL’)) xmlcol 4 FROM user_flashback_txn_report 5 WHERE compensating_xid = ( SELECT xid 6 FROM v$transaction ))) … Read more


In this post I’ll explain how values that get updated back to how the look at the originating transaction are handled. I’ll use three transactions – one inserts a record then a second transaction updates it and then – this is the important part – a third transaction updates the record back to the originally … Read more


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 … Read more


The third mode available for backing out transactions is NOCONFLICT_ONLY mode. It implements the following rule: Only backout transactions for records that were not touched by any subsequent transactions. Here’s an example that inserts 2 records in one transaction and then updates one of them in another. Then an attempt is made to backout the … Read more


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 … Read more


There are 4 modes that TRANSACTION_BACKOUT can be run in. The mode setting determines how the backout reacts to subsequent transactions that operate on any of the records it touches. The default mode is NOCASCADE which implements the following behaviour. If any of the records touched by the transaction being backed out were subsequently touched … Read more

Undo SQL

The TRANSACTION_BACKOUT feature executes SQL to backout or undo ┬áthe specific changes from the desired transactions. For example the UPDATE in the previous post is undone with this SQL statement. update “DRH”.”DEMO” set “DEMO_NAME” = ‘Demo 1’ where “DEMO_ID” = ‘1’ and “DEMO_NAME” = ‘Demo One’; The undo SQL identifies the specific record by including … Read more

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 … Read more

Bringing JSON To The Table

As a long tine use of Relational Databases I live by the rule that data must be stored in and retrieved from table. And those tables must have columns. But JSON plays by it’s own rules by allowing random attribute-value pairs with no consistency enforced. But the latest version of my most used database, Oracle … Read more