How to Stop Auto Refresh of  Materialized view in Oracle


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 we will see:

dbms_refresh.refresh('"[OWNER]"."[MVIEW_NAME]"');
STEP 3. 
Get 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(30,TRUE);
commit;
end;
/


How to start refreshing Mview again just run the job.

begin
dbms_job.run(JOB_ID);
commit;
end;
/
Example:


 
begin
dbms_job.broken(30,FALSE);
commit;
end;
/