Flashback Query

-- Create a test table
 * Prepare a sample 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

-- Using SCN
 * Difference ways to perform Flashback Query.

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