6.5. Using Flashback Transaction Query

The Flashback Transaction Query is designed to be a diagnostic tool to help identify changes made to the database at the transaction level. This tool can be used to perform analysis on data for audits of transactions. With Flashback Transaction Query, you can identify all changes made within a specific time period, as well as perform transactional recovery of tables.

The Flashback Transaction Query is based on undo data and utilizes the UNDO_RETENTION initialization parameter to determine the amount of time to retain committed undo data in the database. The Flashback Transaction Query also uses the RETENTION GUARANTEE in the same manner as the previously discussed Flashback Versions Query.

The Flashback Transaction Query analysis and diagnostics are performed by querying the view FLASHBACK_TRANSACTION_QUERY. The data in this view allow analysis of a specific transaction or what changes were made at a specific time.

Using the FLASHBACK_TRANSACTION_QUERY view can help identify the table and operation that is performed against the table. This view can be large, so it is helpful to use a filter like the transaction identifier, which is in the column XID. The XID value was identified in the previous Flashback Versions Query example.

Let's query the FLASHBACK_TRANSACTION_QUERY to perform transactional analysis by specific transaction:

SQL> select table_name, operation, undo_sql
  2 from flashback_transaction_query
  3 where xid = '020018001F030000';

TABLE_NAME OPERATION UNDO_SQL
---------- --------- --------------------------------------------------
T1         UPDATE   update "TEST"."T1" set "SALARY" = '31000' where
  
ROWID = 'AAAMVBAAEAAAAFlAAC';

Another method is to use timestamps to narrow the transactional analysis to a certain point-in-time. Let's look at an example in more detail:

SQL> select table_name, operation, undo_sql
  2 from flashback_transaction_query
  3 where start_timestamp >= to_timestamp ('2004-10-26 06:45:00',
  
'YYYY-MM-DDHH:MI:SS')
  4 and table_owner = 'TEST';
TABLE_NAME OPERATION UNDO_SQL
---------- --------- --------------------------------------------------
T1         UPDATE update "TEST"."T1" set "SALARY" = '35000' where ROWID
  
 = 'AAAMVBAAEAAAAFlAAC';
T1         UPDATE update "TEST"."T1" set "SALARY" = '31000' where ROWID
  
 = 'AAAMVBAAEAAAAFlAAC';
T1         UPDATE update "TEST"."T1" set "SALARY" = '25000' where ROWID
  
 = 'AAAMVBAAEAAAAFlAAC';
T1         UPDATE update "TEST"."T1" set "SALARY" = '40000' where ROWID
  
 = 'AAAMVBAAEAAAAFlAAC';

SQL>

If you need to perform a transactional recovery of any values for the employee JONES, you can then update the row back to the prior SALARY value that is displayed in FLASHBACK_TRANSACTION_QUERY.

As you can see, the Flashback Transaction Query is a valuable addition to the Flashback Technologies. The Flashback Transactional Query is a diagnostic tool to help identify changes at the transactional level. There are many methods to query FLASHBACK_TRANSACTION_QUERY to see how and when data was changed.