SQL Oracle 使用范围内的时间戳记录历史记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/359733/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle record history using as of timestamp within a range
提问by Zsolt Botykai
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:
我最近了解到 oracle 有一个对我非常有用的功能 - 因为设计者/实现者不太关心数据历史 - 如果它在 oracle 缓存中可用，我可以查询记录的历史状态，如下所示：
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?
但是现在我需要检查一个范围内的历史数据。无论如何，使用缓存有可能吗？
回答by Tony Andrews
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.
请注意，您可以返回多远受到 UNDO_RETENTION 参数的限制，通常为数小时而不是数天。
回答by Justin Cave
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.
需要注意的一点是，这种闪回查询依赖于写入 UNDO 表空间的 UNDO 信息。并且该信息不会永远保留——在合理负载下的大多数生产系统不会有 24 小时可用的 UNDO 信息。
Depending on the Oracle version, you may need to set the UNDO_RETENTIONparameter to a value longer than the time period you are trying to flashback through.
根据 Oracle 版本，您可能需要将该UNDO_RETENTION参数设置为比您尝试闪回的时间段更长的值。
回答by Dave Costa
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).
你可以用这个做更多的事情。查看文档（您可能想找到适合您版本的文档）。
