Oracle Flashback Query (SELECT AS OF)

To use Oracle Flashback Query, use a SELECT statement with an AS OF clause. Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.

Uses of Oracle Flashback Query include:

•  Recovering lost data or undoing incorrect, committed changes. For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
•  Comparing current data with the corresponding data at an earlier time. For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.
•  Checking the state of transactional data at a particular time. For example, you can verify the account balance of a certain day.
•  Simplifying application design by removing the need to store some kinds of temporal data. Oracle Flashback Query lets you retrieve past data directly from the database.
•  Applying packaged applications, such as report generation tools, to past data.
•  Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.

Example of Examining and Restoring Past Data

Suppose that you discover at 12:30 PM that the row for employee Chung was deleted from the employees table, and you know that at 9:30AM the data for Chung was correctly stored in the database. You can use Oracle Flashback Query to examine the contents of the table at 9:30 AM to find out what data was lost. If appropriate, you can restore the lost data.

Example 1 retrieves the state of the record for Chung at 9:30AM, April 4, 2004:

Example 1 Retrieving a Lost Row with Oracle Flashback Query

SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';

Example 2 restores Chung's information to the employees table:

Example 2 Restoring a Lost Row After Oracle Flashback Query

INSERT INTO employees (
  SELECT * FROM employees
  AS OF TIMESTAMP
  TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
  WHERE last_name = 'Chung'
);

Guidelines for Oracle Flashback Query

• You can use the AS OF clause in queries to perform data definition language (DDL) operations (such as creating and truncating tables) or data manipulation language (DML) statements (such as INSERT and DELETE) in the same session as Oracle Flashback Query.
• To use the result of Oracle Flashback Query in a DDL or DML statement that affects the current state of the database, use an AS OF clause inside an INSERT or CREATE TABLE AS SELECT statement.
• If a possible 3-second error (maximum) is important to Oracle Flashback Query in your application, use an SCN instead of a time stamp. 
• You can create a view that refers to past data by using the AS OF clause in the SELECT statement that defines the view.
  If you specify a relative time by subtracting from the current time on the database host, the past time is recalculated for each query. For example:

  CREATE VIEW hour_ago AS
    SELECT * FROM employees
      AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
  

  SYSTIMESTAMP refers to the time zone of the database host environment.
• You can use the AS OF clause in self-joins, or in set operations such as INTERSECT and MINUS, to extract or compare data from two different times.
  You can store the results by preceding Oracle Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement. For example, this query reinserts into table employees the rows that existed an hour ago:

  INSERT INTO employees
      (SELECT * FROM employees
       AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)
      MINUS SELECT * FROM employees;
  

  SYSTIMESTAMP refers to the time zone of the database host environment.

We can use either AS OF TIMESTAMP or AS OF SCN. For example.

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

INSERT INTO flashback_query_test (id) VALUES (1);
COMMIT;

SELECT COUNT(*) FROM flashback_query_test;

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

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

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

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