Flashback – Part 4 (Flashback Transaction Query)

Hi all,

If you have missed the previous Flashback posts, please go through these links to find it and read them if you feel like!

Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 3 (Flashback Versions Query)
Flashback – Part 5 (Flashback Table)
Flashback – Part 6 (Flashback Database)
Flashback – Part 7 (Flashback Data Archive)

And now, we are half way there to the end of the Flashback posts, let’s see a little more about FLASHBACK TRANSACTION QUERY.

Being very simple, Flashback Transactions Query is pretty much the same as Flashback Versions Query, where you can see all changes made between two times in the past. The difference here is that the TRANSACTION query, facilitate the rollback of an operation to us by providing the proper SQL to undo it.

FTQ is also undo-based, so as usual, make sure you have the space on the undo tablespace that fit for you and also the undo_retention that is enough for your scenario.

There are some things that need to be configured before use the FTQ, so make sure it is properly set up:

1. Your DB must be running with version 10.0 compatibility or higher.
2. Supplemental logging must be enabled ( alter database add supplemental log data)

.GRANTS: Any user who might need to use FTQ must have the SELECT ANY TRANSACTION grant, and also the FLASHBACK privilege on those tables that he wants to be able to flashback (or FLASHBACK ANY TABLE).

In order to operate the Flashback Transactions Query, we should use the FLASHBACK_TRANSACTION_QUERY view. This view determines what changes were made in a specific transaction or between two times in the past.  Make sure you set a WHERE clause in your select statement, indicating or the transaction identifier or a timestamp. Let’s have a look into the view columns:

SQL> desc flashback_transaction_query
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 XID                                                RAW(8)
 START_SCN                                          NUMBER
 START_TIMESTAMP                                    DATE
 COMMIT_SCN                                         NUMBER
 COMMIT_TIMESTAMP                                   DATE
 LOGON_USER                                         VARCHAR2(30)
 UNDO_CHANGE#                                       NUMBER
 OPERATION                                          VARCHAR2(32)
 TABLE_NAME                                         VARCHAR2(256)
 TABLE_OWNER                                        VARCHAR2(32)
 ROW_ID                                             VARCHAR2(19)
 UNDO_SQL                                           VARCHAR2(4000)

See the XID column there? This is our Transaction identifier, so how would we know the identifier of our transaction if we don’t have this information?

There are some hidden columns on every table named VERSIONS_% that contain all these informations when we use the VERSIONS BETWEEN, and some of them are named as:

versions_startscn
versions_starttime
versions_endscn
versions_endtime
versions_xid
versions_operation

In order to clarify all of this, let’s use an example to illustrate every statement read here today.

After doing all the pre-requisites described above:

• Compatibility 10.o
• Enable Supplemental log
• Grant SELECT ANY TRANSACTION and FLASHBACK ANY TABLE

Now, we wanna know the values of some of our hidden columns for GREPORA table (created on previous posts), such as VERSIONS_XID, in order to identify the transaction id’s to properly use FTQ. Let’s use the following query to get it:

SELECT versions_startscn,
       versions_starttime,
       versions_endscn,
       versions_endtime,
       versions_xid,
       versions_operation,
       grepora.*
FROM grepora
 VERSIONS BETWEEN TIMESTAMP
         to_timestamp('20/06/2016 16:35:0 0', 'dd/mm/yyyy hh24:mi:ss')
     AND to_timestamp('20/06/2016 16:40:00', 'dd/mm/yyyy hh24:mi:ss');

Obviously,  please adjust your script to run between the desired timestamp.

Once you have the informations captured above, we can figure out the transaction id (XID) and query the FLASHBACK_TRANSACTION_QUERY view, to be able to rollback our transaction:

SELECT XID,undo_sql 
FROM flashback_transaction_query t
WHERE table_owner='RNOLIO' 
and table_name='GREPORA' 
and XID="";

• The output should be such as:

XID                    UNDO_SQL
----------------       ---------------------------
000200030000002D       insert into "RNOLIO"."GREPORA" ("column1","column2","column3") values ('111','Mike','655');

000200030000002D       delete from "RNOLIO"."GREPORA" where ROWID = 'AAAKD4AABAAAJ3BAAB';

000200030000002D       update "RNOLIO"."GREPORA" set "column1" = 'value1' where ROWID = 'AAAKD2AABAAAJ29AAA';

Please note that we have the UNDO_SQL column, indicating to us the exact command to be executed to rollback that exact transation. This is awsome, right? Also, instead of use the XID as a filter, you can use any other hidden column that you want, or even use the timestamp between two points in time.

Please let us know if you have any doubt on this,  and have an awesome week.

Rafael.