Menu
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 ...
253
A materialized view created with the automatic refresh can not be alter to stop refreshing. In order to disable that you must break the dbms_job that was created in order to refresh the view. Mview are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data.
Unlike indexes, materialized views are not automatically updated with every data change. They must explicitly be refreshed, either on every commit, on a periodically time schedule or – typically in data warehouses – at the end of an ETL job.
You can also use v$mvrefresh dynamic performance view to know which MV is being refresh.
When you truncate a table, Oracle Database automatically removes all data in the table's indexes and any materialized view direct-path INSERT information held in association with the table. This information is independent of any materialized view log.
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>.
114
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
