什么会导致 Oracle 10g 中的物化视图停止快速刷新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1392242/
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
What can cause a materialized view in Oracle 10g to stop fast refreshing?
提问by WW.
If I have materialized view in Oracle which is defined as REFRESH FAST ON COMMITevery 15 minutes. It works when initially created and refreshes happily. What can cause it to stop fast refreshing?
如果我在 Oracle 中定义了REFRESH FAST ON COMMIT每 15 分钟的物化视图。它在最初创建时工作并愉快地刷新。什么会导致它停止快速刷新？
I can see that it has stopped refreshing based on this:
我可以看到它基于此停止刷新：
select mview_name, last_refresh_date from all_mviews;
回答by moleboy
Things we've found seem to stop an MV from refreshing: 1. a change to the base table's DDL 2. if the MV is across a DB link, a problem with getting the data across the link (for example, we had a database here, and one remote. If during a refresh, the connection between the two databases failed, then the refresh failed)
我们发现的事情似乎阻止了 MV 的刷新： 1. 对基表的 DDL 的更改 2. 如果 MV 跨数据库链接，则跨链接获取数据的问题（例如，我们有一个数据库这里，一个远程。如果在刷新过程中，两个数据库之间的连接失败，则刷新失败）
In the case of #1, the refresh fails and never ever works again and we have to recreate the MV. In the case of #2, its been unclear if the refresh will ever pick up again. Fast refresh MVs have proved to be unreliable for us so we've created a job in Windows scheduler to initiate our refreshes.
在 #1 的情况下，刷新失败并且永远不会再工作，我们必须重新创建 MV。在#2 的情况下，尚不清楚刷新是否会再次恢复。事实证明，快速刷新 MV 对我们来说是不可靠的，因此我们在 Windows 调度程序中创建了一个作业来启动我们的刷新。
回答by Juergen Hartelt
A thing that occasionally happened during my last job was, that DBA activity sometimes set the parameter job_queue_processesto 0. This will stop materialized view refreshing until the parameter value is set to something greater than 0.
在我上一份工作中偶尔发生的一件事是，DBA 活动有时将参数设置job_queue_processes为 0。这将停止物化视图刷新，直到参数值设置为大于 0 的值。
回答by Gus
It depends on the materialized view. The following query from the oracle data dictionary will give you a list of all your (the oracle user's) materialized views and how fast refreshable they are.
这取决于物化视图。以下来自 oracle 数据字典的查询将为您提供所有（oracle 用户）物化视图的列表以及它们的刷新速度。
SELECT MVIEW_NAME, FAST_REFRESHABLE FROM USER_MVIEWS;
The FAST_REFRESHABLE column will give you one of the following values: NO:The materialized view is not fast refreshable, and hence is complex.
FAST_REFRESHABLE 列将为您提供以下值之一： NO：物化视图不能快速刷新，因此很复杂。
DIRLOAD:Fast refresh is supported only for direct loads.
DIRLOAD：仅直接加载支持快速刷新。
DML:Fast refresh is supported only for DML operations.
DML：仅 DML 操作支持快速刷新。
DIRLOAD_DML:Fast refresh is supported for both direct loads and DML operations.
DIRLOAD_DML：直接加载和 DML 操作都支持快速刷新。
DIRLOAD_LIMITEDDML:Fast refresh is supported for direct loads and a subset of DML operations.
DIRLOAD_LIMITEDDML：直接加载和 DML 操作的子集支持快速刷新。
The ones that have given me problems in the past have been DIRLOAD_LIMITEDDML. I have usually gotten those if there is a COUNT, MAX, etc... in the MVIEW query. Usually these refresh on inserts and deletes but not on updates.
过去给我带来问题的是DIRLOAD_LIMITEDDML。如果在 MVIEW 查询中存在 COUNT、MAX 等，我通常会得到这些。通常这些会在插入和删除时刷新，但不会在更新时刷新。
