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;

/