Flashback Query (AS OF) in Oracle Database

Flashback Query (AS OF) in Oracle Database

Create a test table with no data and check the current SCN and time.

create table flashback_mqm_test (id  NUMBER(10));

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN TO_CHAR(SYSTIMESTAM

----------- -------------------

584777 2020-09-11 23:55:06

Add a row to the test table and commit the change.

INSERT INTO flashback_mqm_test (id) VALUES (1);

COMMIT;

If we check the contents of the table, we can see there is a single row.

SELECT COUNT(*) FROM flashback_mqm_test;

  COUNT(*)

----------

         1

The following two examples use the AS OF clause to query the table using the timestamp and SCN we captured before the row was inserted.

select count(*) from flashback_mqm_test AS OF TIMESTAMP TO_TIMESTAMP('2020-09-11 23:55:06', 'YYYY-MM-DD HH24:MI:SS');

  COUNT(*)

----------

         0

select count(*) from flashback_mqm_test AS OF SCN 584777;

  COUNT(*)

----------

         0

As we can see, the data in the table reflect the point in time specified in the AS OF clause.

There are a few things to consider when using flashback query.

-Flashback query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.

-The flashback operation is performed by winding back the data using the undo information, like a consistent get. The time it takes to wind back the data depends on the number of changes that have happened, so flashback queries of volatile data that go back a long time can be slow.

-Flashback Data Archive (FDA) was introduced in Oracle 11g to allow you to protect UNDO information, allowing guaranteed flashback queries.

-Each table in a query can reference a different point in time.

-The AS OF clause can be included in DML and DDL statements.