How to Enable Flashback Query and Flashback Transaction Query

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.

How to enable Flashback Query

You have to enable the minimum supplemental log before using Flashback 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

How to 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 = 'Chen';

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
        110 John                 Chen

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.

How to enable Flashback Transaction Query

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

How to use Flashback Transaction Query

Let's see how to use it by the following example.

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

SQL> update hr.employees set first_name = 'Ed' where employee_id = 110;

1 row updated.

SQL> commit;

Commit complete.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *