Oracle 11g Broken Materliazed视图：停止刷新而不删除视图或刷新视图？ [英] Oracle 11g Broken Materliazed view: Stop refresh without dropping view or refreshing view?

问题描述

所以我有一个侧面的看法（我知道...）：

So I've got a materalized view(I know...) :

CREATE MATERIALIZED VIEW vw_my_view_here
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 1/24 AS 
/*huge-ass select statement here*/
UNION ALL
/*huge-ass select statement here*/
UNION ALL
/*huge-ass select statement here*/
UNION ALL
/*huge-ass select statement here*/

这已经存在于我们的系统中了一段时间，不会导致问题。这些选择查询中的一个被破坏了（无论如何 - 大概是基础表中的模式更改），并已经运行了24小时直接消耗所有框资源。这是盒子上的其他东西的问题。

This has been present in our system for some time without causing issues. One of these select queries is broken (somehow - presumibly schema changes in the underlying tables) and has taken to running for 24 hours straight and consuming all of the boxes resources. This is a problem for the other things on the box.

我不能删除或修改视图 - 因为它完全没有记录（我知道...），我需要弄清楚谁消费它，它是如何填充，等等。

I can't drop or modify the view - as it's completely undocumented (I know...) and I need to figure out who consumes it, how it's populated, etc. first.

我试图杀死正在处理查询的进程，但似乎另一个查询会立即被触发。

I tried killing the process that's handling the query, but it seems another query is fired straight away afterwards.

如何停止实体化视图，而不是1）删除视图和（2）不刷新视图（因为这需要24小时以上的所有框资源） 。

How can I stop the materialized view without 1) dropping the view and (2) without refreshing the view (because that's taking 24+ hours and all of our box resources).

我已尝试执行

 alter materialized view view_name refresh on demand;

但它似乎只是无休止地运行，对原始的大规模查询没有影响。

but it seems to just run endlessly with no effect on the original massive query.

有什么想法吗？

推荐答案

更改刷新设置说：

此子句仅设置默认刷新选项。有关实际实现刷新的说明，请参阅Oracle数据库高级复制和Oracle数据库数据仓库指南。

This clause only sets the default refresh options. For instructions on actually implementing the refresh, refer to Oracle Database Advanced Replication and Oracle Database Data Warehousing Guide.

c> alter materialized view 语句不会自己刷新。它只是在等待当前排除刷新完成之后，才能更新该视图的数据字典。一旦当前刷新完成（或终止）， alter 将完成，并防止进一步的自动刷新开始。

So your alter materialized view statement is not doing a refresh itself. It is just waiting for the currently-excuting refresh to complete before it can update the data dictionary for that view. Once the current refresh completes (or is terminated) the alter will complete and prevent further automatic refreshes from being started.

这篇关于Oracle 11g Broken Materliazed视图：停止刷新而不删除视图或刷新视图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！