Flashback Query

To find all the changes to the table during a certain period, issue the following query.

SQL> SELECT empno, ename, versions_starttime, versions_startscn, versions_endtime, versions_endscn, versions_xid, versions_operation
FROM emp
VERSIONS BETWEEN SCN 3816659800 AND 3816659980
/*where empno = 7900*/

Using the above query with where clause enables you to view the history of a table row over a period of time.

When the versions_endtime is null it means that the rows has not been changed yet.

In addition to SCN you can also use the timestamp as shown below.

SQL> SELECT empno, ename, sal, versions_starttime, versions_startscn, versions_endtime, versions_endscn, versions_xid, versions_operation
FROM emp
VERSIONS BETWEEN TIMESTAMP to_date('26/03/2009 09:45:00', 'dd/mm/yyyy hh24:mi:ss') and to_date('26/03/2009 09:52:00', 'dd/mm/yyyy hh24:mi:ss')
/*where empno = 7900*/

To view status of table at a particular time.

SQL> SELECT * FROM emp
AS OF TIMESTAMP to_date('26/03/2009 09:30:00', 'dd/mm/yyyy hh24:mi:ss');

Thanks