知道在 Oracle 中是否更新了记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2155029/
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
Know if a record is updated within Oracle?
提问by R van Rijn
Is there a option to see if existing table/record from a Oracle database is updated?
是否可以选择查看 Oracle 数据库中的现有表/记录是否已更新？
回答by Lou
From a monitoring perspective (not intended to find previous changes), you have several options including but not limited to triggers, streams, and a column with a default value of sysdate. A trigger will allow you to execute a bit of programming logic (stored directly in the trigger or in an external database object) whenever the record changes (insert, update, delete). Streams can be used to track changes by monitoring the redo logs. One of the easiest may be to add a date column with a default value of sysdate.
从监控的角度（不打算查找以前的更改），您有几个选项，包括但不限于触发器、流和默认值为 sysdate 的列。触发器将允许您在记录更改（插入、更新、删除）时执行一些编程逻辑（直接存储在触发器或外部数据库对象中）。流可用于通过监视重做日志来跟踪更改。最简单的方法之一可能是添加一个默认值为 sysdate 的日期列。
回答by APC
Are you talking about within a transaction or outside of it?
你是在交易内还是在交易外？
Within our program we can use things like SQL%ROWCOUNT to see whether our DML succeeded...
在我们的程序中，我们可以使用 SQL%ROWCOUNT 之类的东西来查看我们的 DML 是否成功...
SQL> set serveroutput on size unlimited
SQL> begin
2 update emp
3 set job = 'SALESMAN', COMM=10
4 where empno = 8083;
5 dbms_output.put_line('Number of records updated = '||sql%rowcount);
6 end;
7 /
Number of records updated = 1
PL/SQL procedure successfully completed.
SQL>
Alternatively we might test for SQL%FOUND (or SQL%NOTFOUND).
或者，我们可以测试 SQL%FOUND（或 SQL%NOTFOUND）。
From outside the transaction we can monitor ORA_ROWSCN to see whether a record has changed.
从事务外部，我们可以监视 ORA_ROWSCN 以查看记录是否已更改。
SQL> select ora_rowscn from emp
2 where empno = 8083
3 /
ORA_ROWSCN
----------
83828715
SQL> update emp
2 set comm = 25
3 where empno = 8083
4 /
1 row updated.
SQL> commit
2 /
Commit complete.
SQL> select ora_rowscn from emp
2 where empno = 8083
3 /
ORA_ROWSCN
----------
83828780
SQL>
By default ORA_ROWSCN is set at the block level. If you want to track it at the lower level your need to create the table with the ROWDEPENCIES keyword.
默认情况下，ORA_ROWSCN 设置在块级别。如果要在较低级别跟踪它，则需要使用 ROWDEPENCIES 关键字创建表。
These are ad hoc solutions. If you want to proactive monitoring then you need to implementing some form of logging. Using triggers to write log records is a common solution. If you have Enterprise Edition you should consider using Fine Grained Auditing: Dan Morgan's library has a useful demo of how to use FGA to track changes.
这些是临时解决方案。如果您想要主动监控，那么您需要实施某种形式的日志记录。使用触发器写入日志记录是一种常见的解决方案。如果您有企业版，您应该考虑使用细粒度审计：Dan Morgan 的库有一个关于如何使用 FGA 跟踪更改的有用演示。
回答by Stephen ODonnell
You can see if a table definition has change by querying the last_ddl_time from the user_objects view.
您可以通过从 user_objects 视图查询 last_ddl_time 来查看表定义是否已更改。
Without using triggers or materialized logs (which would be a total hack) there is no way I know of to see when any particular row in a table has been updated.
如果不使用触发器或物化日志（这将完全是一种黑客行为），我无法知道表中的任何特定行何时被更新。
