如何找出上次更新 Oracle 表的时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/265122/
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 find out when an Oracle table was updated the last time
提问by Maximilian
Can I find out when the last INSERT, UPDATE or DELETE statement was performed on a table in an Oracle database and if so, how?
我能否找出对 Oracle 数据库中的表执行最后一次 INSERT、UPDATE 或 DELETE 语句的时间，如果是，如何查明？
A little background: The Oracle version is 10g. I have a batch application that runs regularly, reads data from a single Oracle table and writes it into a file. I would like to skip this if the data hasn't changed since the last time the job ran.
一点背景：Oracle 版本是 10g。我有一个定期运行的批处理应用程序，从单个 Oracle 表读取数据并将其写入文件。如果自上次作业运行以来数据没有更改，我想跳过此步骤。
The application is written in C++ and communicates with Oracle via OCI. It logs into Oracle with a "normal" user, so I can't use any special admin stuff.
该应用程序是用 C++ 编写的，并通过 OCI 与 Oracle 通信。它以“普通”用户登录 Oracle，因此我无法使用任何特殊的管理工具。
Edit: Okay, "Special Admin Stuff" wasn't exactly a good description. What I mean is: I can't do anything besides SELECTing from tables and calling stored procedures. Changing anything about the database itself (like adding triggers), is sadly not an option if want to get it done before 2010.
编辑：好的，“特殊管理人员”并不是一个很好的描述。我的意思是：除了从表中选择和调用存储过程之外，我不能做任何事情。如果想在 2010 年之前完成更改数据库本身的任何内容（如添加触发器），遗憾的是不是一个选项。
回答by Paul
I'm really late to this party but here's how I did it:
我参加这个聚会真的很晚，但我是这样做的：
SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from myTable;
It's close enough for my purposes.
这对我的目的来说已经足够接近了。
回答by Justin Cave
Since you are on 10g, you could potentially use the ORA_ROWSCNpseudocolumn. That gives you an upper bound of the last SCN (system change number) that caused a change in the row. Since this is an increasing sequence, you could store off the maximum ORA_ROWSCNthat you've seen and then look only for data with an SCN greater than that.
由于您使用的是 10g，因此您可能会使用ORA_ROWSCN伪列。这为您提供了导致行更改的最后一个 SCN（系统更改编号）的上限。由于这是一个递增序列，您可以存储ORA_ROWSCN您所看到的最大值，然后仅查找 SCN 大于该值的数据。
By default, ORA_ROWSCNis actually maintained at the block level, so a change to any row in a block will change the ORA_ROWSCNfor all rows in the block. This is probably quite sufficient if the intention is to minimize the number of rows you process multiple times with no changes if we're talking about "normal" data access patterns. You can rebuild the table with ROWDEPENDENCIESwhich will cause the ORA_ROWSCNto be tracked at the row level, which gives you more granular information but requires a one-time effort to rebuild the table.
默认情况下，ORA_ROWSCN实际上是在块级别维护的，因此对块中任何行的更改都会更改块ORA_ROWSCN中所有行的 。如果我们谈论的是“正常”数据访问模式，那么如果目的是尽量减少多次处理的行数而不进行任何更改，这可能就足够了。您可以重建表，ROWDEPENDENCIES这将导致ORA_ROWSCN在行级别跟踪 ，这为您提供更细粒度的信息，但需要一次性努力来重建表。
Another option would be to configure something like Change Data Capture (CDC) and to make your OCI application a subscriber to changes to the table, but that also requires a one-time effort to configure CDC.
另一种选择是配置更改数据捕获 (CDC) 之类的内容，并使您的 OCI 应用程序成为对表更改的订阅者，但这也需要一次性配置 CDC。
回答by dub
Ask your DBA about auditing. He can start an audit with a simple command like :
向您的 DBA 咨询有关审计的问题。他可以使用一个简单的命令开始审计，例如：
AUDIT INSERT ON user.table
Then you can query the table USER_AUDIT_OBJECT to determine if there has been an insert on your table since the last export.
然后您可以查询表 USER_AUDIT_OBJECT 以确定自上次导出以来您的表中是否有插入。
google for Oracle auditing for more info...
google for Oracle 审计以获取更多信息...
回答by grokster
SELECT * FROM all_tab_modifications;
回答by mwilliams
Could you run a checksum of some sort on the result and store that locally? Then when your application queries the database, you can compare its checksum and determine if you should import it?
您能否对结果运行某种校验和并将其存储在本地？然后当你的应用程序查询数据库时，你可以比较它的校验和并确定是否应该导入它？
It looks like you may be able to use the ORA_HASHfunction to accomplish this.
看起来您可以使用ORA_HASH函数来完成此操作。
Update:Another good resource: 10g's ORA_HASH function to determine if two Oracle tables' data are equal
更新：另一个很好的资源：10g 的 ORA_HASH 函数来确定两个 Oracle 表的数据是否相等
回答by mwilliams
Oracle can watch tables for changes and when a change occurs can execute a callback function in PL/SQL or OCI. The callback gets an object that's a collection of tables which changed, and that has a collection of rowid which changed, and the type of action, Ins, upd, del.
Oracle 可以监视表的更改，并且在发生更改时可以在 PL/SQL 或 OCI 中执行回调函数。回调获取一个对象，该对象是一组已更改的表，该对象具有一组已更改的 rowid，以及操作类型 Ins、upd、del。
So you don't even go to the table, you sit and wait to be called. You'll only go if there are changes to write.
所以你甚至不去桌子，你坐在那里等着被叫。只有在有变化要写的时候你才会去。
It's called Database Change Notification. It's much simpler than CDC as Justin mentioned, but both require some fancy admin stuff. The good part is that neither of these require changes to the APPLICATION.
它称为数据库更改通知。正如贾斯汀所说，它比 CDC 简单得多，但两者都需要一些花哨的管理内容。好的部分是这些都不需要更改应用程序。
The caveat is that CDC is fine for high volume tables, DCN is not.
需要注意的是，CDC 适用于大容量表，而 DCN 则不然。
回答by Osama Al-Maadeed
You would need to add a trigger on insert, update, delete that sets a value in another table to sysdate.
您需要在插入、更新、删除时添加一个触发器，将另一个表中的值设置为 sysdate。
When you run application, it would read the value and save it somewhere so that the next time it is run it has a reference to compare.
当您运行应用程序时，它会读取该值并将其保存在某处，以便下次运行时它有一个引用进行比较。
Would you consider that "Special Admin Stuff"?
你会考虑那个“特殊的管理工具”吗？
It would be better to describe what you're actually doing so you get clearer answers.
最好描述一下您实际在做什么，以便获得更清晰的答案。
回答by hui chen
If the auditing is enabled on the server, just simply use
如果在服务器上启用了审计，只需简单地使用
SELECT *
FROM ALL_TAB_MODIFICATIONS
WHERE TABLE_NAME IN ()
回答by Tony Andrews
How long does the batch process take to write the file? It may be easiest to let it go ahead and then compare the file against a copy of the file from the previous run to see if they are identical.
批处理需要多长时间写入文件？最简单的方法是让它继续运行，然后将文件与上次运行的文件副本进行比较，看看它们是否相同。
回答by TMtech
If any one is still looking for an answer they can use Oracle Database Change Notificationfeature coming with Oracle 10g. It requires CHANGE NOTIFICATIONsystem privilege. You can register listeners when to trigger a notification back to the application.
如果有人仍在寻找答案，他们可以使用Oracle 10g 附带的 Oracle数据库更改通知功能。它需要CHANGE NOTIFICATION系统权限。您可以注册侦听器何时触发返回应用程序的通知。
