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?

Answer 1

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.