Flashback Query - Revision history

Ittichai at 14:16, 11 March 2011

2011-03-11T14:16:29Z

Ittichai at 14:16, 11 March 2011

2011-03-11T14:16:04Z

Ittichai at 14:15, 11 March 2011

2011-03-11T14:15:43Z

Ittichai: Created page with "* 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, 'YY..."

2011-03-11T14:14:58Z

Created page with "* Demo <pre> -- 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, 'YY..."

New page

* Demo
<pre>
-- 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
</pre>

@@ Line 1: / Line 1: @@
-* Demo
+* Prepare a sample table
 <pre>
 -- Create a test table
@@ Line 25: / Line 25: @@
 ----------
+</pre>
 * Difference ways to perform Flashback Query
@@ Line 53: / Line 53: @@
 </pre>

@@ Line 2: / Line 2: @@
 <pre>
 -- Create a test table
 CREATE TABLE tbl_flashback_test(
    id  NUMBER(10)
@@ Line 7: / Line 8: @@
 -- Check the current date/time or SCN
 SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
 FROM v$database;
@@ Line 15: / Line 17: @@
 -- Perform an action (insert)
 SQL> INSERT INTO tbl_flashback_test(id) VALUES (1);
 COMMIT;
@@ Line 20: / Line 23: @@
 -- Check the LATEST content
 SQL> SELECT COUNT(*) FROM tbl_flashback_test;
@@ Line 30: / Line 34: @@
 <pre>
 -- Using SCN
 SELECT COUNT(*) FROM tbl_flashback_test
    AS OF SCN 722452;
@@ Line 38: / Line 43: @@
 -- 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');
@@ Line 46: / Line 52: @@
 -- Using TIMESTAMP and INTERVAL
 SELECT COUNT(*) FROM tbl_flashback_test
     AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '4' MINUTE;