Flashback Query

• Prepare a sample table.

-- Create a test table

CREATE TABLE tbl_flashback_test(
  id  NUMBER(10)
);

-- Check the current date/time or SCN

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

CURRENT_SCN 	TO_CHAR(SYSTIMESTAM
----------- 	-------------------
     722452 	2008-09-03 10:22:37

-- Perform an action (insert)

SQL> INSERT INTO tbl_flashback_test(id) VALUES (1);
COMMIT;


-- Check the LATEST content

SQL> SELECT COUNT(*) FROM tbl_flashback_test;

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

• Difference ways to perform Flashback Query.

-- Using SCN

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

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

-- Using TIMESTAMP

SELECT COUNT(*) FROM tbl_flashback_test
   AS OF TIMESTAMP TO_TIMESTAMP('2008-09-03 10:22:37', 'YYYY-MM-DD HH24:MI:SS');

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

-- Using TIMESTAMP and INTERVAL

SELECT COUNT(*) FROM tbl_flashback_test
   AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '4' MINUTE;

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