A materialized view create with the automatic refresh can not be alter to stop refreshing. In order to do that you must break the dbms_job that was created in order to refresh the view.
Follow the steps for 12.2.0.1:
1. Connect as mview owner.
2. select * from user_jobs where broken ='N';
3. In the WHAT column for the mview refresh job you will see:
dbms_refresh.refresh('"[OWNER]"."[MVIEW_NAME]"');
4. Take the JOB_ID form the job column and execute:
begin
dbms_job.broken(JOB_ID,TRUE);
commit;
end;
/
And the mview will stop refreshing automatically.
If you want the mview to start refreshing again just run the job.
begin
dbms_job.run(JOB_ID);
commit;
end;
/
Then the materialized view will refresh and continue refreshing by the interval.
