If you have missed the previous Flashback posts, please go through these links to find it and read them if you feel like!
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:
- Your DB must be running with version 10.0 compatibility or higher.
- 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.