Oracle Disable Materialized View Refresh

oracle materialized-views
Jeff picture Jeff · Mar 1, 2016 · Viewed 12.6k times · Source

Anyone have the syntax to disable the refresh of a materialized view in Oracle? I struggle with the Oracle documentation and I'm not finding an online example.

I understand it starts something like: ALTER MATERIALIZED VIEW view_name ...

Answer

Alex Poole picture Alex Poole · Mar 1, 2016

If it is currently set to refresh on commit, you can change it to refresh on demand, which means you need to explcitly refresh it via dbms_mview, with:

alter materialized view view_name refresh on demand;

This seems fairly clear in the documentation.

If you really want to permanently disable refresh, even manually, so you're left with the current contents and they can not be updated from the view query, you can drop the materialized view but keep the backing table:

drop materialized view view_name preserve table;

This is obviously more radical. And view_name will now just be an ordinary table, essentially as if you'd done create table view_name as <view query>.

Related questions

What is the difference between Views and Materialized Views in Oracle?

What is the difference between Views and Materialized Views in Oracle?

Read More
How to refresh materialized view in oracle

Iam trying to refresh the materialized view by using: DBMS_MVIEW.REFRESH('v_materialized_foo_tbl') But it's throwing invalid sql statement. Then I have created a stored procedure like this: CREATE OR REPLACE PROCEDURE MAT_VIEW_FOO_TBL IS …

Read More
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 …

Read More