Difference between revisions of "Flashback Query"
Jump to navigation
Jump to search
Line 1: | Line 1: | ||
− | * Prepare a sample table | + | * Prepare a sample table. |
<pre> | <pre> | ||
-- Create a test table | -- Create a test table | ||
Line 31: | Line 31: | ||
</pre> | </pre> | ||
− | * Difference ways to perform Flashback Query | + | * Difference ways to perform Flashback Query. |
<pre> | <pre> | ||
-- Using SCN | -- Using SCN |
Latest revision as of 09:16, 11 March 2011
- 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