Wednesday, October 7, 2009

Stop mview from auto refreshing

A materialized view create with the automatic refresh can not be alter to stop refreshing. In order to do that you must broke the dbms_job that was created in order to refresh the view.

Connect as mview owner.

select * from user_jobs where broken ='N';

In the WHAT column for the mview refresh job you will see :

dbms_refresh.refresh('"[OWNER]"."[MVIEW_NAME]"');

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 mview will refresh and continue refreshing by the interval.

4 comments:

  1. Thank you. BTW,I think to rerun the mview, the best way to do is enable the broken job and automatically it will get refreshed soon.

    begin
    dbms_job.broken(job_id, false);
    commit;
    end;
    /

    ReplyDelete
  2. I created materialized view but job is not created automatically . what could be the reason ?

    ReplyDelete
    Replies
    1. For refreshing materiailzed view , we should have dba_job or dba_schedular.

      Some times jobs are created automatically with the table creation but without job creation .

      what would be the reason ?

      Delete
    2. If you created a materialized view and you did not defined to refresh in interval then a job is not created !

      Delete