SQL Oracle：如何在架构中找到上次更新（任何表）的时间戳？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6775671/
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
Oracle: How to find the timestamp of the last update (any table) within a schema?
提问by Vladimir Dyuzhev
There is an Oracle database schema (very small in data, but still about 10-15 tables). It contains a sort of configuration (routing tables).
有一个 Oracle 数据库模式（数据非常小，但仍然大约有 10-15 个表）。它包含一种配置（路由表）。
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.
如果任何表有任何更新，应用程序应将所有表重新加载到内存中。
What would be the most effective way to check the whole schema for update since a given key point (time or transaction 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.
我想象 Oracle 为每个架构保留一个事务 ID。然后应该有一种方法来查询这样的 ID 并保留它以在下一次轮询时进行比较。
I've found this question, where such an pseudo-column exists on a row level:
我发现了这个问题，在行级别存在这样的伪列：
How to find out when an Oracle table was updated the last time
I would think something similar exists on a schema level.
我认为在模式级别上存在类似的东西。
Can someone please point me in the right direction?
有人可以指出我正确的方向吗？
回答by Allan
I'm not aware of any such functionality in Oracle.See below.
我不知道 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 contextwith 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.
我能想出的最佳解决方案是在每个表上创建一个触发器，用当前日期/时间更新单行表或上下文。这样的触发器可以在表级别（而不是行级别），因此它们不会像大多数触发器那样承担那么多的开销。
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.
顺便提一下，Oracle 不能为每个模式保留一个事务 ID，因为一个事务可能会影响多个模式。可能可以使用 V$ 视图将事务跟踪回它所影响的对象，但这并不容易，而且几乎肯定会比触发器方案的性能更差。
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):
事实证明，如果您有 10g，您可以使用 Oracle 的闪回功能来获取此信息。但是，您需要启用闪回（它会带来一些它自己的开销）并且查询速度非常慢（大概是因为它并不是真正用于此用途）：
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.
避免这些问题的更好解决方案是从触发器中插入一行。这不会锁定表，因此不会有任何序列化并且不需要异步进行插入，因此它们可以与实际数据一起回滚（并且在您的应用程序中不可见，直到数据也是可见的）。应用程序将获得最大值，如果该表被索引，这应该会非常快（实际上，该表将是索引组织表的理想候选者）。唯一的缺点是您需要定期运行以清除旧值的作业，因此它不会变得太大。
回答by Tim
dbms_stats.gather_table_statsmight also help: http://forums.oracle.com/forums/thread.jspa?threadID=607610
dbms_stats.gather_table_stats也可能有帮助：http: //forums.oracle.com/forums/thread.jspa?threadID=607610
4. Statistics is considered to be stale, when the change is over 10% of current rows.
(As of 11g, this value can be customized per objects. Cool feature)
.
.
.
exec dbms_stats.gather_table_stats(user, 'T_STAT');
select * from sys.dba_tab_modifications where table_name = 'T_STAT';
No row selected
select stale_stats from sys.dba_tab_statistics where table_name = 'T_STAT';
NO
insert into t_stat select rownum from all_objects where rownum <= 20;
select * from sys.dba_tab_modifications where table_name = 'T_STAT';
No rows selected <-- Oops
select stale_stats from sys.dba_tab_statistics where table_name = 'T_STAT';
NO <-- Oops
exec dbms_stats.flush_database_monitoring_info;
select * from sys.dba_tab_modifications where table_name = 'T_STAT';
TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS
UKJA T_STAT 20 0 0 2008-01-18 PM 11:30:19 NO 0
select stale_stats from sys.dba_tab_statistics where table_name = 'T_STAT';
YES
