Oracle record history using as of timestamp within

Question

I recently learnt that oracle has a feature which was pretty useful to me - as the designer/implementator didn't care much about data history - I can query the historical state of a record if it's available yet in the oracle cache, like this:

select * 
  from ( select * 
           from sometable where some_condition ) 
as of timestamp sysdate-1 

But now I need to check the historical data within a range. Is it possible anyhow, using the cache?

Answer 1

Yes, like this:

SQL> select sal from emp where empno=7369;

       SAL
----------
      5800

SQL> update emp set sal = sal+100 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> update emp set sal = sal-100 where empno=7369;

1 row updated.      

SQL> commit;

Commit complete.

SQL> select empno, sal, versions_starttime,versions_xid
  2  from emp
  3  versions between timestamp sysdate-1 and sysdate
  4  where empno=7369;

     EMPNO        SAL VERSIONS_STARTTIME                                                          VERSIONS_XID
---------- ---------- --------------------------------------------------------------------------- --
      7369       5900 11-DEC-08 16.05.32                                                          0014001300002A74
      7369       5800 11-DEC-08 16.03.32                                                          000D002200012EB1
      7369       5800

Note that how far back you can go is limited by the UNDO_RETENTION parameter, and will typically be hours rather than days.

Answer 2

One note to be aware of is that this sort of flashback query relies on UNDO information that is written to the UNDO tablespace. And that information is not retained forever-- most production systems under reasonable load are not going to have 24 hours of UNDO information available.

Depending on the Oracle version, you may need to set the UNDO_RETENTION parameter to a value longer than the time period you are trying to flashback through.

Answer 3

SELECT *
  FROM sometable
  VERSIONS BETWEEN TIMESTAMP systimestamp - 1 AND systimestamp

will give you all versions of all rows in the last day.

There's lots more you can do with this. Check out the documentation (you may want to find the docs for your version).