Oracle 11g Broken Materialized view: Stop refresh without dropping view or refreshing view?
<p>So I've got a materalized view (I know...) : </p>

<pre><code>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*/
</code></pre>

<p>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. </p>

<p>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. </p>

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

<p>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). </p>

<p>I've tried running</p>

<pre><code> alter materialized view view_name refresh on demand;
</code></pre>

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

<p>Any ideas? </p>