Oracle 11g破碎的实体化视图:是否在不删除视图或刷新视图的情况下停止刷新? [英] Oracle 11g Broken Materialized 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.
有什么想法吗?
