Oracle:如何查找模式中最后一次更新(任何表)的时间戳? [英] Oracle: How to find the timestamp of the last update (any table) within a schema?
问题描述
有一个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:
我认为在模式级别上也存在类似的东西.
I would think something similar exists on a schema level.
有人可以指出我正确的方向吗?
Can someone please point me in the right direction?
推荐答案
我不知道Oracle中的任何此类功能.参见下文.
我能想到的最好的解决方案是在每个表上创建一个触发器，以更新单行表或
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.
事实证明，如果您有10克，则可以使用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.
这篇关于Oracle:如何查找模式中最后一次更新(任何表)的时间戳?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
