Flashback Query
Jump to navigation
Jump to search
- 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