Transaction Backout Does Not Do JSON

I was testing transaction backout with non-scalar datatypes. And since I’m doing JSON research I decided to try JSON columns. I discovered that transaction backout does not work with JSON. Not sure just why yet. Here is the test code I used. SQL> CREATE TABLE JSON 2 ( always_json CLOB 3 CONSTRAINT keep_it_real 4* CHECK … Read more

When Views Are Forced To Care

In my last post regarding Edition Based Redefinition (EBR) I explained that Oracle views do not automatically take on changes to the structures underneath them. This makes them a very handy feature. But they do have limitations – such as when an new NOT NULL column is added. Here is an example: — create a … Read more

Views Don’t Care Unless They Have To

Oracle’s Edition Based Redefinition (EBR) feature allows modifying database objects such as PLSQL code or database tables without interfering with a functioning application. In this series of articles I’ll introduce some of the underlying concepts of EBR and show some examples of its use. This first post describes a feature of Oracle views that helps … Read more

Transaction Backout Is Not Picky

All of the examples I’ve shown so far on this blog rely on transaction names to specify what to backout. This is simpler than specifying transaction ID’s. But it does have potential drawback. It backs out ALL discovered. Here is an example that executes single INSERTs in 3 separate transactions – notice the transaction names … Read more

Best Dad In The World

I have many things to be thankful for but two of the most important are the town I grew up in and the parents that raised me there. The town is the idyllic Nelson, BC – a great place to grow up. And my parents allowed me to take full advantage of it including the … Read more

Answer: Backout And Constraints

And here are the results. Two modes raised the error during backout and 2 succeeded. NOCASCADE Raised Error NOCASCADE_FORCE Raised Error CASCADE No Error 2 Records Afterwards DEMO_ID DEMO_NAME ———- ——— 2 Demo 2 1 Demo 1 SQL Executed delete from DRH.DEMO where DEMO_ID = ‘1’ and DEMO_NAME = ‘Demo 1’ insert into DRH.DEMO(DEMO_ID,DEMO_NAME) values … Read more

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

NOCASCADE_FORCE vs NONCONFLICT_ONLY

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

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