Oracle使用范围内的时间戳记记录历史记录 [英] Oracle record history using as of timestamp within a range

问题描述

我最近了解到oracle具有一项对我非常有用的功能-因为设计者/实现者不太在意数据历史记录-我可以查询记录的历史状态(如果该记录在oracle缓存中仍然可用)，像这样:

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?

推荐答案

是的，就像这样:

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

请注意，您可以走多远受UNDO_RETENTION参数的限制，通常为几小时而不是几天.

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

这篇关于Oracle使用范围内的时间戳记记录历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！