Flashback Version

• 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(id) VALUES (1);
COMMIT;

• Insert a new record with ID=3.

SQL> INSERT INTO tbl_flashback_test (id) VALUES (3);

1 row created.

SQL> commit;

Commit complete.

• Check for Versions. Note that these VERSIONS_* are the virtual columns - similar to ROWID.

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

• Perform an update, and check Versions again. Note the values in the VERSIONS_OPERATION column.

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.

• Other Syntaxes

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');