A materialized view is a table segment or database object that contains the results of a query. 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. Materialized views, which store data based on remote tables are also, know as snapshots.
Steps to Disable Automatic Refresh of Materialized View.
STEP 1. Connect as mview owner and execute the below query.
select * from user_jobs where broken ='N';
STEP 2. In the WHAT column for the mview refresh job you will see:
dbms_refresh.refresh('"[OWNER]"."[MVIEW_NAME]"');
STEP 3. Take the JOB_ID form the job column and execute below procedure and mview will stop refreshing automatically:
begin
dbms_job.broken(JOB_ID,TRUE);
commit;
end;
/
Example:
begin
dbms_job.broken(25,TRUE);
commit;
end;
/
If you want the mview to start refreshing again just run the job.
begin
dbms_job.run(JOB_ID);
commit;
end;
/
Example:
begin
dbms_job.broken(25,FALSE);
commit;
end;
/
That’s all about disabling the materialized view. If you want to stop materialized view from auto refresh just run the above procedure and check the status of the job