This post is actually an extension of another post: How to Enable Flashback Database. I recommend it to you before reading this post. However, it's not a restriction.
Flashback Query
Flashback Query allows you to use SELECT statements with an AS OF clause to retrieve data as it existed at a specific earlier time. You can think of Flashback Query as a snapshot of a specific table at a specific time or SCN in the past.
Enable Flashback Query
Flashback Query is based on UNDO data. That is to say, you can run a flashback query as long as your UNDO space and UNDO_RETENTION is enough to support the past point in time.
Use Flashback Query
SQL> select employee_id, first_name, last_name from hr.employees as of timestamp to_timestamp('2018-05-28 20:00:00', 'yyyy-mm-dd hh24:mi:ss') where last_name = 'Rowe';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
110 John Rowe
In the above query, I want to see the snapshot of table hr.employees at 2018-05-28 20:00:00 in the past.
Please note that, the query only shows committed data at the point of time.
Flashback Transaction Query
Flashback Transaction Query generates a dynamic view called FLASHBACK_TRANSACTION_QUERY which contains historical metadata and undo statements for a given time interval or a given specific transaction ID according to your query.
In terms of time, you can think of Flashback Query is a point in time of the table, whereas Flashback Transaction Query shows changes on the table in a time-line fashion.
Before enabling the feature, you have to make sure that the database is in archivelog mode.
Enable Flashback Transaction Query
You have to enable the minimum supplemental log before using Flashback Transaction Query like this:
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
In addition to the minimum supplemental log for Flashback Query, you need to add supplemental log for primary keys and foreign keys in order to trace their changes in Flashback Transaction Query.
SQL> select supplemental_log_data_pk, supplemental_log_data_fk from v$database;
SUP SUP
--- ---
NO NO
SQL> alter database add supplemental log data (primary key) columns;
Database altered.
SQL> alter database add supplemental log data (foreign key) columns;
Database altered.
SQL> select supplemental_log_data_pk, supplemental_log_data_fk from v$database;
SUP SUP
--- ---
YES YES
Use Flashback Transaction Query
Let's see how to use it by the following example.
First of all, we format the date time of current session.
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2018/05/28 20:15:02
Then we perform an update and commit it.
SQL> update hr.employees set first_name = 'Scott' where employee_id = 110;
1 row updated.
SQL> commit;
Commit complete.
Le's see the undo SQL.
SQL> select operation, undo_sql from flashback_transaction_query where table_owner = 'HR' and table_name = 'EMPLOYEES' and start_timestamp > to_date('2018-05-28 20:15:00', 'yyyy-mm-dd hh24:mi:ss');
OPERATION
--------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
UPDATE
update "HR"."EMPLOYEES" set "FIRST_NAME" = 'John' where ROWID = 'AAAVTFAAFAAAADP
AAK';
How convenient! The result of the last query contains an undo SQL statement for us to revert the transaction if we prefer to undo the transaction by ourselves.
Disable Flashback Transaction Query
To turn off flashback transaction query, we drop supplement logs we added previously.
SQL> alter database drop supplemental log data (foreign key) columns;
Database altered.
SQL> alter database drop supplemental log data (primary key) columns;
Database altered.
SQL> alter database drop supplemental log data (unique) columns;
Database altered.
SQL> alter database drop supplemental log data;
Database altered.
Further Reading: How to Revert INSERT INTO by DELETE FROM Statements.