Flashback Query

• Demo

-- 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
<pre>
-- 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