8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Flashback Query (AS OF) in Oracle Database 10g

Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK functionality or Oracle 9i, but in a more convenient form.

Flashback Query
Considerations

Flashback Query

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

CREATE TABLE flashback_query_test (
  id  NUMBER(10)
);

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

CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
     722452 2004-03-29 13:34:12

SQL>

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

INSERT INTO flashback_query_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_query_test;

  COUNT(*)
----------
         1

SQL>

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_query_test AS OF TIMESTAMP TO_TIMESTAMP('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS');

  COUNT(*)
----------
         0

SQL>


SELECT COUNT(*)
FROM   flashback_query_test AS OF SCN 722452;

  COUNT(*)
----------
         0

SQL>

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

Considerations

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.