物化视图-确定最后一次刷新 [英] Materialized Views - Identifying the last refresh

问题描述

我目前访问一系列视图和实例化视图.物化由第三方维护，该第三方几乎没有提供有关物化的频率和成功的信息.最近，物化视图未能刷新，我发送了许多报告，其中包含不正确/延迟的数据.

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.

是否有一种方法可以确定是否已使用Oracle系统表刷新了实例化视图?如果没有，有没有人知道我无需联系第三方该怎么做?

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?

推荐答案

看来doc_180已经在评论中回答了这个问题，我不确定为什么没有将它添加为答案

It looks like doc_180 already answered this in the comments, I'm not sure why it didn't get added as the answer

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>>

如果您有权访问DBA表，则可以将DBA_MVIEWS替换为ALL_MVIEWS.这样一来，您就可以访问有关何时刷新每个实例化视图的信息，而不仅仅是访问您可以访问的实例化视图的子集.当然，在这种情况下，这可能并不是特别重要的区别.

If you have access to the DBA tables, you could substitute DBA_MVIEWS for 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.

这篇关于物化视图-确定最后一次刷新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！