Ittichai at 14:21, 11 March 2011

2011-03-11T14:21:55Z

Ittichai at 14:21, 11 March 2011

2011-03-11T14:21:35Z

Ittichai: Created page with "* Prepare a sample table.

 -- Create a test table  CREATE TABLE tbl_flashback_test(   id  NUMBER(10) );  -- Perform an action (insert)  SQL> INSERT INTO tbl_flashback_test(i..."

2011-03-11T14:21:15Z

Created page with "* Prepare a sample table. <pre> -- Create a test table CREATE TABLE tbl_flashback_test( id NUMBER(10) ); -- Perform an action (insert) SQL> INSERT INTO tbl_flashback_test(i..."

New page

* Prepare a sample table.
<pre>
-- Create a test table

CREATE TABLE tbl_flashback_test(
id NUMBER(10)
);

-- Perform an action (insert)

SQL> INSERT INTO tbl_flashback_test(id) VALUES (1);
COMMIT;
</pre>
* Insert a new record with ID=3.
<pre>
SQL> INSERT INTO tbl_flashback_test (id) VALUES (3);

1 row created.

SQL> commit;

Commit complete.
</pre>
* Check for Versions. Note that these VERSIONS_* are the virtual columns - similar to ROWID.
<pre>
SQL> select VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_XID, VERSIONS_OPERATION, ID
from tbl_flashback_test
VERSIONS BETWEEN TIMESTAMP MINVALUE and MAXVALUE
order by VERSIONS_STARTTIME;

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ID
---------------------- ---------------------- ---------------- - ----------
03-SEP-08 10.39.00 AM 1A000E00E4CB0000 I 1
03-SEP-08 10.41.15 AM 1400240064CF0000 I 3
</pre>

* Perform an update, and check Versions again. Note the values in the VERSIONS_OPERATION column.
<pre>
SQL> UPDATE tbl_flashback_test set id=2 where id=1;

1 row updated.

SQL> commit;

Commit complete.

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V ID
---------------------- ---------------------- ---------------- - ----------
03-SEP-08 10.39.00 AM 03-SEP-08 10.42.09 AM 1A000E00E4CB0000 I 1
03-SEP-08 10.41.15 AM 1400240064CF0000 I 3
03-SEP-08 10.42.09 AM 1A000400E6CB0000 U 2
3 rows selected.
</pre>
* Other Syntaxes
</pre>
select ID, versions_starttime, versions_endtime
from tbl_flashback_test
VERSIONS between SCN 1000 and 1001;

select ID, versions_starttime, versions_endtime
from tbl_flashback_test
VERSIONS between timestamp
to_date('12/1/2003 15:57:54','mm/dd/yyyy hh24:mi:ss')
and to_date('12/1/2003 16:57:55','mm/dd/yyyy hh24:mi:ss');
</pre>

[[Category:Database_Features]]

Flashback Version - Revision history

Ittichai at 14:21, 11 March 2011

Ittichai at 14:21, 11 March 2011

Ittichai: Created page with "* Prepare a sample table.
-- Create a test table CREATE TABLE tbl_flashback_test( id NUMBER(10) ); -- Perform an action (insert) SQL> INSERT INTO tbl_flashback_test(i..."

@@ Line 53: / Line 53: @@
 </pre>
 * Other Syntaxes
-</pre>
+<pre>
 select ID, versions_starttime, versions_endtime
 from tbl_flashback_test

@@ Line 1: / Line 1: @@
 * Prepare a sample table.
 <pre>
--- Create a test table
+-- Create a test table.
 CREATE TABLE tbl_flashback_test(
@@ Line 7: / Line 7: @@
 );
--- Perform an action (insert)
+-- Perform an action (insert).
 SQL> INSERT INTO tbl_flashback_test(id) VALUES (1);