Oracle records history using a timestamp within a range

Question

I recently found out that oracle has a function that is very useful to me, since the developer / developer did not really care about the data history - I can request the historical state of the record, if it is still available in the oracle cache, for example:

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

But now I need to check the historical data within the range. Is this possible using a 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 usually be hours, not days.

Answer 2

One point to keep in mind is that such a flashback request relies on UNDO information that is written in the UNDO tablespace. And this information is not saved forever - most production systems under reasonable load will not have UNDO information available in 24 hours.

Depending on your version of Oracle, you may need to set the UNDO_RETENTION parameter to a value that exceeds the period of time that you are trying to make a callback.

Answer 3

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

will provide you with all versions of all rows for the last day.

You can do a lot with this. Check out the documentation (you can find documents for your version).