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

Question

I'm Trying to create Materialized View which will be updated every 5 minute automatically, I need update based on Mview log table.

I have created Materialized view log on TABLE1 TABLE1.SQL Script

CREATE MATERIALIZED VIEW LOG ON TABLE1;   -- MLOG$_TABLE1

Then I've created Materialized View

CREATE MATERIALIZED VIEW JIBO_MVIEW 
REFRESH START WITH SYSDATE NEXT SYSDATE +5/24/60 
ENABLE QUERY REWRITE AS
      SELECT O.ID
            ,O.DATETIME_CREATED
            ,O.ORIGINATOR
            ,O.DETAILS
            ,O.PAYMENT_REF
        FROM TABLE1 O
       WHERE O.ORIGINATOR LIKE '53%';

after changing some value In TABLE1, new Record is inserted MLOG$_TABLE1 log table

but changed value is not updated in Materialized view (JIBO_MVIEW). (even after one day :) )

As I checked in Alert Log there is problem with auto generated DBMS_JOB, it fails on every executions.

 - ORA-12012: error on auto execute of job 4263
 - ORA-00942: table or view does not exist
 - ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
 - ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
 - ORA-06512: at "SYS.DBMS_IREFRESH", line 689
 - ORA-06512: at "SYS.DBMS_REFRESH", line 195 - 
 - ORA-06512: at line 1

but if I execute job manually it works just fine. Here is code example

BEGIN DBMS_REFRESH.REFRESH('"JIBO"."JIBO_MVIEW"');
COMMIT;
END;

Then I have added new grants to user

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE MATERIALIZED VIEW TO JIBO; 
GRANT CREATE ANY TABLE TO JIBO WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO JIBO;
GRANT SELECT ON SCHEMA1.MLOG$_TABLE1 TO JIBO WITH GRANT OPTION;
GRANT ALL ON SCHEMA1.TABLE1 TO JIBO WITH GRANT OPTION;

and now I am getting this error while job executes automatically

 - ORA-12012: error on auto execute of job 4287
 - ORA-01031: insufficient privileges
 - ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
 - ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
 - ORA-06512: at "SYS.DBMS_IREFRESH", line 689
 - ORA-06512: at "SYS.DBMS_REFRESH", line 195
 - ORA-06512: at line 1

Please, helm me to solve this issue. is this problem due to incorrectly created Materialized view, or maybe it's due to some parameter in oracle which should be turned on?

At first site everything is ok, maybe changed record doesn't meet `WHERE O.ORIGINATOR LIKE '53%'` condition. — Seyran, Apr 12 '17 at 12:16
I have record in Materialized view (JIBO_MVIEW) but it does not changes after changing value in source table (TABLE1) for this record. — JiboOne, Apr 12 '17 at 12:19
yes, the record which i have attached as a picture to my question is still there. — JiboOne, Apr 12 '17 at 12:33
Your example works for me. But I had to fill in some details about the original table and values. It might help to create a fully reproducible test case so we can completely recreate all details. Also, what happens when you try to manually refresh the job with code like this: `begin dbms_refresh.refresh(user||'."JIBO_MVIEW"'); end; /`? — Jon Heller, Apr 25 '17 at 23:48
As I see problem didn't solve, maybe there is a problem with access rights. Try to create public synonim for MV and log table. — Seyran, Apr 26 '17 at 05:58
What is your Oracle version? Does the issue reproduce in an empty schema with the table and mview only? — B Samedi, Apr 27 '17 at 00:27
@JonHeller Manual Update worked but automatic job does not works. what could be a reason? Maybe job could not make commit or something like this? — JiboOne, May 16 '17 at 14:18
@BSamedi Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production — JiboOne, May 16 '17 at 14:19
This question could still benefit from a Short, Self Contained, Correct (Compilable), Example. — Jon Heller, May 16 '17 at 15:47
I have shown Compilable example in my question, materialized view works but as it seems DBMS_JOB has problems while execution. i have added grant scrips also in question window — JiboOne, May 16 '17 at 16:01
@BSamedi I've added a link of TABLE1 script in question form — JiboOne, May 17 '17 at 07:07
I cannot see the grants for the materialized view itself, have you tried that too? — maraca, May 20 '17 at 19:34

Seyran · Answer 1 · 2017-04-12T13:06:20.873

4

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 this select * from V$PARAMETER where name = 'job_queue_processes' parameter value, is it ok, not exceeded.

edited Apr 12 '17 at 13:06

answered Apr 12 '17 at 12:52

Seyran

711
4
8

job is broken due to failures (16), I've recreated the materialized view, did not change anything in source table(TABLE1), but job still fails. which parameter should i check in V$PARAMETER view? – JiboOne Apr 12 '17 at 15:44
How many user jobs you have, maybe problem in jobs count, they are trying to exceed the value of `job_queue_processes` then you add one more? – Seyran Apr 12 '17 at 15:50
I have only this job for my user, but in database there is many other job. result of `select * from V$PARAMETER where name = 'job_queue_processes'` query -------- > NUM 1872, NAME job_queue_processes, VALUE 20, DISPLAY_VALUE 20,ISDEFAULT FALSE,ISSES_MODIFIABLE FALSE,ISSYS_MODIFIABLE IMMEDIATE, ISINSTANCE_MODIFIABLE TRUE,ISMODIFIED FALSE,ISADJUSTED FALSE,ISDEPRECATED FALSE, ISBASIC FALSE, DESCRIPTION maximum number of job queue slave processes, UPDATE_COMMENT ,HASH 1663833312 – JiboOne Apr 12 '17 at 16:00
I think problem in this parameter, `job_queue_processes` as I know is common for all users jobs, so you have only 20 jobs to run for your oracle instance, try to increase `alter system set job_queue_processes = 1000`. And then recreate your view. – Seyran Apr 12 '17 at 16:08
i have increased the limit, then create mview again, but job still fails. – JiboOne Apr 12 '17 at 16:17
Try to restart database, referring to oracle docs they say some parameters take effect after restart. – Seyran Apr 12 '17 at 17:47
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/141621/discussion-between-seyran-and-jibo). – Seyran Apr 13 '17 at 07:41

score 2 · Answer 2 · answered May 18 '17 at 11:43

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;

score 1 · Answer 3 · answered May 02 '17 at 09:13

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

no now i have another error: `ORA-12012: error on auto execute of job 4287 ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025 ORA-06512: at "SYS.DBMS_IREFRESH", line 689 ORA-06512: at "SYS.DBMS_REFRESH", line 195 ORA-06512: at line 1 ` — JiboOne, May 16 '17 at 15:15
Grants I have added by SYS user on my MV user: `GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE MATERIALIZED VIEW TO JIBO; GRANT CREATE ANY TABLE TO JIBO WITH ADMIN OPTION; GRANT UNLIMITED TABLESPACE TO JIBO; GRANT SELECT ON SCHEMA1.MLOG$_EBPP_PAYMENTS TO JIBO WITH GRANT OPTION; GRANT ALL ON RTPSEBPP.EBPP_PAYMENTS TO UFC_DAVITJ WITH GRANT OPTION;` — JiboOne, May 16 '17 at 15:25

score 0 · Answer 4 · edited May 27 '17 at 08:10

In a similar setup I have grant execute to all the sys objects below. Maybe this will solve your problem.

DBMS_DEFER_SYS,  
DBMS_SNAPSHOT,  
DBMS_SNAP_REPAPI,  
DBMS_REPCAT_DECL,  
DBMS_DEFER_QUERY,  
DBMS_REPCAT_UTL,  
DBMS_DEFER_INTERNAL_QUERY,  
DBMS_DEFER_INTERNAL_SYS,  
DBMS_REPCAT_UNTRUSTED,  
DBMS_OFFLINE_SNAPSHOT,  
DBMS_REPCAT,  
DBMS_INTERNAL_REPCAT,  
DBMS_REPCAT_RPC,  
DBMS_RECTIFIER_DIFF,  
DBMSOBJGWRAPPER,  
DBMS_REPCAT_RGT,  
DBMS_OFFLINE_OG

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

4 Answers4