Create Oracle Materialized View to be refreshed every 5 minute Using materialized view log

I'm not sure but problem may be in refresh job creation for MV. try this queries.

select * from user_jobs where what like 'dbms_refresh.refresh%'; what is the value of BROKEN column?

select * from user_snapshot_refresh_times;and see thisselect * from V$PARAMETER where name = 'job_queue_processes' parameter value, is it ok, not exceeded.

You can have Oracle manage the refresh job on it's own by creating a refresh group as follows

exec DBMS_REFRESH.MAKE(name => 'MY_RG', list => 'JIBO_MVIEW', next_date => SYSTIMESTAMP, interval => 'SYSTIMESTAMP + Interval ''5'' minute');

After that you can check the status of the refresh group using

select * from user_refresh;

Hi here are some general hints that might help you solving your permissions problem (table or view does not exist).

grant create any table, CREATE MATERIALIZED VIEW to MV_USERwhere MV_USER is the OWNER of the MATERIALIZED VIEW.

Connect as that same MV_USER.

Then do your CREATE MATERIALIZED VIEW.

To fix your broken jobs, connect as the MV_USER (owner of the MV).

Then run:

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;                                                                     /