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 )))
 7    WHERE INSTR(XMLTYPE.GETCLOBVAL(column_value),'exec="yes"') > 0
 8 /

OH_YEAH
 --------------------------------------------------------------------------------
delete from "DRH"."DEMO" where "DEMO_ID" = '1' and "DEMO_NAME" = 'Demo 1'

There’s a lot going on here so I’ll try to explain.

1. The innermost select queries the UNDO SQL for the currently executed (but not committed) transaction. The result is in XML format
2. The XMLSEQUENCE function translates the XML into a varray of records which are passed out to the outermost select
3. The EXTRACTVALUE function extracts the UNDO_SQL (/USQL) value and displays it

Queries like this increase my respect for XML developers because that seems like a lot of work to extract some text. XML also gives me a new-found respect for the forward slash key – it must get very tired when doing XML.

Thanks for reading