Oracle Flashback Version Query

Through flashback version query we can retrieve the different versions of specific rows that existed during a given time interval.
A new row version is created whenever a COMMIT statement is executed.
Flashback version query we need to use VERSIONS BETWEEN clause with SELECT statement

Select … VERSIONS {between {SCN TIMESTAMP} start AND end}
We can retrieve data based on SCN or TIMESTAMP.
Where start and end are expressions representing the start and end of the time interval to be queried

Example
SQL> select * from taj;

NAME NO
-------------------- ----------
TAJ 1
TAJ 2
TAJ 3

SQL> delete taj where no = 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into taj values ('TAJ',4);

1 row created.

SQL> commit;

Commit complete.

SQL> update taj set NAME='tajA' WHERE NO=2;

0 rows updated.

SQL> COMMIT;

Commit complete.


SQL> select name, no from TAJ
2 versions between timestamp
3 to timestamp ('2008-05-15 20:06:00','YYYY-MM-DD HH24:MI:SS')
4 AND to timestamp ('2008-05-15 20:09:00','YYYY-MM-DD HH24:MI:SS');
NAME NO
-------------------- ----------
TAJ 4
TAJ 2
TAJ 1
TAJ 2
TAJ 3

NOTE: We getting all transaction which performed on TAJ table during 8:06 pm to 08:09 pm.

But if we want to know exact time , scn or DML operation type then we can use below pseudo columns with above query

1. VERSIONS_STARTSCN or VERSIONS_STARTTIME
Above column return SCN or TIME when row version was created

2. VERSIONS_ENDSCN or VERSIONS_ENDTIME
Above column return SCN or TIME when row version was expired or if we getting NULL means row version was current at the time of query or the row corresponds to a DELETE operation.

3. VERSIONS_OPERATION
Above column return DML operation type 1. I for INSERT, U for UPDATE, D for DELETE

4. VERSIONS_XID
Above column return IDENTIFIER when row version was created, this identifier is use in FLASHBACK TRANSACTION QUERY where we can get “actual” executed SQL statement which row version created.

Example with VERSIONS_OPERATION

SQL> select name, no, versions_operation, versions_starttime from taj
2 versions between timestamp
3 to timestamp ('2008-05-15 20:06:00','YYYY-MM-DD HH24: MI: SS’)
4 AND to timestamp ('2008-05-15 20:09:00','YYYY-MM-DD HH24: MI: SS’);

NAME NO V VERSIONS_STARTTIME
-------------------- ---------- - ------------------------------
TAJ 4 I 15-MAY-08 08.07.22 PM
TAJ 2 D 15-MAY-08 08.07.10 PM
TAJ 1
TAJ 2
TAJ 3