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

Home » Articles » 10g » Here

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.

Related articles.

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.