Difference between revisions of "Flashback Query"
Jump to navigation
Jump to search
(One intermediate revision by the same user not shown) | |||
Line 1: | Line 1: | ||
− | * Prepare a sample table | + | * Prepare a sample table. |
<pre> | <pre> | ||
-- Create a test table | -- Create a test table | ||
+ | |||
CREATE TABLE tbl_flashback_test( | CREATE TABLE tbl_flashback_test( | ||
id NUMBER(10) | id NUMBER(10) | ||
Line 7: | Line 8: | ||
-- Check the current date/time or SCN | -- Check the current date/time or SCN | ||
+ | |||
SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') | SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') | ||
FROM v$database; | FROM v$database; | ||
Line 15: | Line 17: | ||
-- Perform an action (insert) | -- Perform an action (insert) | ||
+ | |||
SQL> INSERT INTO tbl_flashback_test(id) VALUES (1); | SQL> INSERT INTO tbl_flashback_test(id) VALUES (1); | ||
COMMIT; | COMMIT; | ||
Line 20: | Line 23: | ||
-- Check the LATEST content | -- Check the LATEST content | ||
+ | |||
SQL> SELECT COUNT(*) FROM tbl_flashback_test; | SQL> SELECT COUNT(*) FROM tbl_flashback_test; | ||
Line 27: | Line 31: | ||
</pre> | </pre> | ||
− | * Difference ways to perform Flashback Query | + | * Difference ways to perform Flashback Query. |
<pre> | <pre> | ||
-- Using SCN | -- Using SCN | ||
+ | |||
SELECT COUNT(*) FROM tbl_flashback_test | SELECT COUNT(*) FROM tbl_flashback_test | ||
AS OF SCN 722452; | AS OF SCN 722452; | ||
Line 38: | Line 43: | ||
-- Using TIMESTAMP | -- Using TIMESTAMP | ||
+ | |||
SELECT COUNT(*) FROM tbl_flashback_test | SELECT COUNT(*) FROM tbl_flashback_test | ||
AS OF TIMESTAMP TO_TIMESTAMP('2008-09-03 10:22:37', 'YYYY-MM-DD HH24:MI:SS'); | AS OF TIMESTAMP TO_TIMESTAMP('2008-09-03 10:22:37', 'YYYY-MM-DD HH24:MI:SS'); | ||
Line 46: | Line 52: | ||
-- Using TIMESTAMP and INTERVAL | -- Using TIMESTAMP and INTERVAL | ||
+ | |||
SELECT COUNT(*) FROM tbl_flashback_test | SELECT COUNT(*) FROM tbl_flashback_test | ||
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '4' MINUTE; | AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '4' MINUTE; |
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