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.
