Created a Materialized View in ORACLE which won’t refresh

materialized-vieworacle

I created a materialized view log along with a materialized view called update_nboe based on table NBOE_ EMPLOYEES_TEST using the following code

CREATE MATERIALIZED VIEW LOG ON NBOE_EMPLOYEES_TEST WITH primary key;

CREATE MATERIALIZED VIEW update_nboe
REFRESH FAST ON DEMAND
AS
SELECT E.EMP_ID, E.USERNAME ,E.NAME, E.LOCATION , E.TITLE, E.LOCATION_CODE, E.RS_GROUP
FROM NBOE_EMPLOYEES_TEST E;

Then I updated NBOE_EMPLOYEES_TEST by inserting additional records hoping that the materialized view would update and refresh on-demand after using the following piece of code

exec dbms_mview.refresh('update_nboe',atomic_refresh_test=>TRUE);

However, I see a red cross on my connections panel for the materialized view and it won't refresh either.

Would appreciate some input.

Best Answer

We normally use jobs to automate MV refreshes, so you don't have to do them manually or on demand.

For example, if you want your MV to refresh every 4 hours, then you would submit a job like this. Connect as the schema owner of the MV.

VARIABLE jobno NUMBER
BEGIN
   DBMS_JOB.SUBMIT
      (job  => :jobno,
      what => 'begin DBMS_MVIEW.REFRESH(''UPDATE_NBOE'',''C''); end;',
      next_date => SYSDATE,
      interval => 'trunc(SYSDATE+1) + 04/24');
   COMMIT;
END;
/
PRINT jobno

The C denotes a complete refresh and you can use F for a fast refresh or check the docs for other possible parameters. I don't know why you chose atomic_refresh_test; this does not seem to be something you need.

Sometimes the jobs break and you would have to run the job again after refreshing the MV manually. This you can check from DBA_JOBS. This is code that checks for broken MV jobs and outputs the commands to fix them. Connect as the owner of the MV then run this code:

set serveroutput on size 1000000
declare
v_mview VARCHAR2(30);
v_run  VARCHAR2(100);
v_job number;
cursor user_mviews_c is
select mview_name from user_mviews ;
cursor user_jobs_c is
select job from user_jobs ;
begin
open user_mviews_c;
loop
   fetch user_mviews_c into v_mview;
   exit when user_mviews_c%notfound;
   v_run := 'exec DBMS_MVIEW.REFRESH ('''||v_mview||''',''C'');';
   dbms_output.put_line(v_run);
end loop;
close user_mviews_c;
open user_jobs_c;
loop
   fetch user_jobs_c into v_job;
   exit when user_jobs_c%notfound;
   v_run := 'exec DBMS_JOB.RUN ('||v_job||');';
   dbms_output.put_line(v_run);
end loop;
close user_jobs_c;
end;
/
Related Question