6

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

Jeff
  • 8,020
  • 34
  • 99
  • 157

1 Answers1

10

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

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Does the first option "alter materialized view view_name refresh on demand" force the view to refresh before the operation completes? – Paul Nov 08 '16 at 10:08
  • @Paul - [from the docs](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2002.htm#i2226546): "This clause only sets the default refresh options. For instructions on actually implementing the refresh, refer to ...", which suggests not. I haven't verified though. – Alex Poole Nov 08 '16 at 10:11
  • Thanks for the quick reply! If you've got any insights on my question I'd greatly appreciate them: http://stackoverflow.com/questions/40484564/oracle-11g-broken-materliazed-view-stop-refresh-without-dropping-view-or-refres – Paul Nov 08 '16 at 10:43