oracle 物化视图 - 识别上次刷新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5798894/
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
Materialized Views - Identifying the last refresh
提问by Scott
I presently access a series of views and materialized views. The materialized are maintained by a third party who offers little information regarding the frequency and success of the materialization. Of late the materialized views have failed to refresh and I have sent out numerous reports with incorrect/delayed data contained within.
我目前访问了一系列视图和物化视图。物化由第三方维护，该第三方几乎不提供有关物化的频率和成功的信息。最近物化视图未能刷新，我发送了大量报告，其中包含不正确/延迟的数据。
At present I am querying each materialized I intend to use to establish when the latest update occurred within the transactional system, if it has not been refreshed then the rest of the code does not execute, however this a lot of wasted effort and can sometimes lead to an incorrect assumption (the materialized view may have been refreshed, but there were no additional transactions made - therefore the remainder of the code does not execute) and I would prefer another method.
目前我正在查询每个我打算用来建立事务系统中最新更新发生时间的物化，如果它没有被刷新，那么其余的代码不会执行，但是这会浪费很多精力，有时会导致错误的假设（物化视图可能已刷新，但没有进行额外的事务 - 因此代码的其余部分不会执行），我更喜欢另一种方法。
Is there a way to identify whether a materialized view has been refreshed using an Oracle system table? If not, does anyone have any ideas how I would do this without having to contact the third party?
有没有办法确定物化视图是否已使用 Oracle 系统表刷新？如果没有，有没有人知道如何在无需联系第三方的情况下做到这一点？
回答by Justin Cave
It looks like doc_180 already answered this in the comments, I'm not sure why it didn't get added as the answer
看起来 doc_180 已经在评论中回答了这个问题，我不确定为什么它没有被添加为答案
SELECT owner, mview_name, last_refresh_date
FROM all_mviews
WHERE owner = <<user that owns the materialized view>>
AND mview_name = <<name of the materialized view>>
If you have access to the DBA tables, you could substitute DBA_MVIEWSfor ALL_MVIEWS. That would allow you to get access to information about when every materialized view was refreshed rather than just the subset of materialized views that you have access to. Of course, that's probably not a difference that is particularly important in this case.
如果你有机会获得DBA表，你可以代替DBA_MVIEWS的ALL_MVIEWS。这将允许您访问有关何时刷新每个物化视图的信息，而不仅仅是您有权访问的物化视图的子集。当然，在这种情况下，这可能不是特别重要的差异。
回答by ptr
Unfortunately oracles default date format is YYYY-MM-DD. If you need the time just use something like this:
不幸的是，oracles 的默认日期格式是 YYYY-MM-DD。如果您需要时间，请使用以下内容：
SELECT owner, mview_name, to_char(LAST_REFRESH_DATE, 'yyyy-mm-dd hh24:mi:ss') last_refresh_date
FROM all_mviews
WHERE owner = 'FOO'
AND mview_name = 'MV_BAR';
回答by Hugo Rodrigues
Personally i use the all_snapshots, and this information is complementary to the information of the DBA_MVIEWS or ALL_MVIEWS
我个人使用all_snapshots，这些信息是对DBA_MVIEWS 或ALL_MVIEWS 信息的补充
select owner, name, last_refresh, error, status, refresh_mode
from all_snapshots
where owner = 'owner';
