Oracle:如何在模式中找到上次更新(任何表)的时间戳?
有一个 Oracle 数据库模式(数据非常少，但仍有大约 10-15 个表).它包含一种配置(路由表).
There is an Oracle database schema (very small in data, but still about 10-15 tables). It contains a sort of configuration (routing tables).
有一个应用程序必须不时轮询此架构.不得使用通知.
There is an application that have to poll this schema from time to time. Notifications are not to be used.
如果架构中的数据未更新，应用程序应使用其当前的内存版本.
If no data in the schema were updated, the application should use its current in-memory version.
如果任何表有任何更新，应用程序应将所有表重新加载到内存中.
If any table had any update, the application should reload all the tables into memory.
自给定关键点(时间或事务 ID)以来检查整个架构是否更新的最有效方法是什么?
What would be the most effective way to check the whole schema for update since a given key point (time or transaction id)?
我想象 Oracle 为每个模式保留一个事务 ID.那么应该有一种方法可以查询这样的 ID 并保留它以在下次投票时进行比较.
I am imagined Oracle keeps an transaction id per schema. Then there should be a way to query such an ID and keep it to compare with at next poll.
我发现了这个问题，这样的伪列存在于行级别:
I've found this question, where such an pseudo-column exists on a row level:
如何查找上次更新 Oracle 表时退出
我认为架构级别存在类似的东西.
I would think something similar exists on a schema level.
有人可以指点我正确的方向吗?
Can someone please point me in the right direction?
推荐答案
我不知道 Oracle 中有任何此类功能.见下文.
我能想到的最佳解决方案是在您的每个表上创建一个触发器来更新单行表或 context 与当前日期/时间.这样的触发器可以在表级(而不是行级)，因此它们不会像大多数触发器那样承担太多的开销.
The best solution I can come up with is to create a trigger on each of your tables that updates a one-row table or context with the current date/time. Such triggers could be at the table-level (as opposed to row-level), so they wouldn't carry as much overhead as most triggers.
顺便说一下，Oracle 不能为每个架构保留一个事务 ID，因为一个事务可能会影响多个架构.或许可以使用 V$ 视图将事务跟踪回它所影响的对象，但这并不容易，而且几乎可以肯定它的性能比触发方案差.
Incidentally, Oracle can't keep a transaction ID per schema, as one transaction could affect multiple schemas. It might be possible to use V$ views to track a transaction back to the objects it affected, but it wouldn't be easy and it would almost certainly perform poorer than the trigger scheme.
事实证明，如果你有 10g，你可以使用 Oracle 的闪回功能来获取这些信息.但是，您需要启用闪回(这会带来一些自己的开销)并且查询速度非常慢(可能是因为它并不是真正用于此用途):
It turns out, if you have 10g, you can use Oracle's flashback functionality to get this information. However, you'd need to enable flashback (which carries some overhead of it's own) and the query is ridiculously slow (presumably because it's not really intended for this use):
select max(commit_timestamp)
from FLASHBACK_TRANSACTION_QUERY
where table_owner = 'YOUR_SCHEMA'
and operation in ('INSERT','UPDATE','DELETE','MERGE')
<小时>
为了避免最后更新"表中的锁定问题，您可能希望将该更新放入使用自治事务的过程中，例如:
In order to avoid locking issues in the "last updated" table, you'd probably want to put that update into a procedure that uses an autonomous transaction, such as:
create or replace procedure log_last_update as
pragma autonomous_transaction;
begin
update last_update set update_date = greatest(sysdate,update_date);
commit;
end log_last_update;
这将导致您的应用程序在某种程度上序列化:需要调用此过程的每个语句都需要等到前一个语句完成.上次更新"表也可能不同步，因为即使激活触发器的更新被回滚，其上的更新也会持续存在.最后，如果您有一个特别长的交易，应用程序可能会在交易完成之前获取新的日期/时间，从而达到目的.我想得越多，就越觉得这是个坏主意.
This will cause your application to serialize to some degree: each statement that needs to call this procedure will need to wait until the previous one finishes. The "last updated" table may also get out of sync, because the update on it will persist even if the update that activated the trigger is rolled back. Finally, if you have a particularly long transaction, the application could pick up the new date/time before the transaction is completed, defeating the purpose. The more I think about this, the more it seems like a bad idea.
避免这些问题的更好解决方案是从触发器中插入一行.这不会锁定表，因此不会有任何序列化并且插入不需要异步进行，因此它们可以与实际数据一起回滚(并且在您的应用程序之前不可见)数据也是可见的).应用程序将获得最大值，如果表被索引，这应该非常快(事实上，这个表将是索引组织表的理想候选者).唯一的缺点是您需要定期运行以清除旧值的作业，因此它不会变得太大.
The better solution to avoid these issues is just to insert a row from the triggers. This would not lock the table, so there wouldn't be any serialization and the inserts wouldn't need to be made asynchronously, so they could be rolled back along with the actual data (and wouldn't be visible to your application until the data is visible as well). The application would get the max, which should be very fast if the table is indexed (in fact, this table would be an ideal candidate for an index-organized table). The only downside is that you'd want a job that runs periodically to clean out old values, so it didn't grow too large.
相关文章