DB:: Stop and Start Auto Refresh of Materialized View

I recently had an requirement to stop auto refresh of materialized view for a while. I followed below steps without making any changes to materialized view DDL.

This approach is helpful, if materialized views are based on database links and respective target systems are going down for maintenance.

1. Login to Database as Materialized View Owner.


Stop MV Refresh:

2. Run following anonymous block to stop auto refresh of all the materialized views belongs to logged in user.

DECLARE

   CURSOR cur_job
   IS
        SELECT  job
        FROM user_jobs
        WHERE broken ='N';

BEGIN

    FOR job_rec in cur_job
    LOOP
        dbms_job.broken(job_rec.job,TRUE);
        COMMIT;
    END LOOP;

END;

Start MV Refresh:

3. Run following anonymous block to start auto refresh of all the materialized views belongs to logged in user.

DECLARE

   CURSOR cur_job
   IS
        SELECT  job
        FROM user_jobs
        WHERE broken ='Y';

BEGIN

    FOR job_rec in cur_job
    LOOP
        dbms_job.run(job_rec.job);
        COMMIT;
    END LOOP;

END;