如何查看自 Oracle 10 中上次提交以来更改的所有记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2203929/
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
How to see all records changed since last commit in Oracle 10?
提问by daveslab
I have a rather large Oracle PL/SQL script I'm testing and I wanted to know if it was possible to see what records were updated/deleted/inserted since the last commit to the database? I need a faster way to check that all the database actions were done correctly. I have access to the command line as well as Oracle's custom tool SQL Developer.
我有一个相当大的 Oracle PL/SQL 脚本正在测试，我想知道是否有可能看到自上次提交到数据库以来更新/删除/插入了哪些记录？我需要一种更快的方法来检查所有数据库操作是否正确完成。我可以访问命令行以及 Oracle 的自定义工具SQL Developer。
回答by Pavel Mitrofanov
In Oracle 10g (and starting with 9i, I think) you may use Flashback Query for this.
在 Oracle 10g（我认为从 9i 开始）中，您可以为此使用闪回查询。
Normally, Flashback Query is used when you need to see data as it were some time ago, but in your case the trick is that Flashback Query sees only committeddata.
通常，当您需要查看一段时间前的数据时，会使用闪回查询，但在您的情况下，技巧是闪回查询仅查看已提交的数据。
So, here's a quick example:
所以，这是一个简单的例子：
SQL> create table t1 as select level lev from dual connect by level < 100;
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
99
SQL> select count(*) from t1 as of timestamp systimestamp;
COUNT(*)
----------
99
SQL> update t1 set lev = -lev;
99 rows updated.
SQL> select max(lev) from t1 as of timestamp systimestamp;
MAX(LEV)
----------
99
SQL> select max(lev) from t1;
MAX(LEV)
----------
-1
SQL> commit;
Commit complete.
SQL> select max(lev) from t1 as of timestamp systimestamp;
MAX(LEV)
----------
-1
SQL>
UPD:even better, you can use Flashback Version Queryor Flashback Transaction Query with some tweaking to filter changes made by all sessions except your current session.
UPD：更好的是，您可以使用闪回版本查询或闪回事务查询进行一些调整来过滤除当前会话之外的所有会话所做的更改。
回答by Jens Schauder
If your environment allows it, you could add triggers to each and every table, creating some kind of audit log.
如果您的环境允许，您可以向每个表添加触发器，创建某种审计日志。
There is an audit feature in oracle, which you might be able to use. Google thinks, this article might be of some help: http://www.securityfocus.com/infocus/1689
oracle 中有一个审计功能，您可以使用它。谷歌认为，这篇文章可能会有所帮助：http: //www.securityfocus.com/infocus/1689
回答by Jens Schauder
You need to better define your issue - Are you looking to write a PLSQL script that captures only the data that has been updated since the last run? Does the record set that you are looking at have a unique id that is sequential? Can you afford to select possible duplicates and validate them with the final set to see if they are indeed duplicates and thus discard them? Depending on these cases, the complexity of your solution will change
您需要更好地定义您的问题 - 您是否希望编写一个 PLSQL 脚本来仅捕获自上次运行以来已更新的数据？您正在查看的记录集是否具有连续的唯一 ID？你能负担得起选择可能的重复项并用最终集验证它们是否确实是重复项并因此丢弃它们吗？根据这些情况，您的解决方案的复杂性会发生变化
回答by pablochan
I might be wrong, but I don't think that there's an easy way to do this. The only thing that comes to mind is checking the redo log but thres no interface for the user to check the operations. You can do it manuallybut it's not that simple.
我可能是错的，但我认为没有简单的方法可以做到这一点。唯一想到的是检查重做日志，但没有用户检查操作的界面。您可以手动完成，但没那么简单。
