Oracle Materialized View

Question

I need your help with a issue on Oracle Materialized View.

I created the Materialized View using the following command:

CREATE MATERIALIZED VIEW SNAP_TEST
TABLESPACE tbs
NOCACHE
LOGGING
NOPARALLEL
REFRESH COMPLETE
START WITH TO_DATE('10-nov-2011 07:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE+1  
AS 
SELECT item
from item@abc;

If I manually refresh it everything works properly. But it doesn't work automatically: I expected an auto-refresh everyday at 7:00, but it never happened. Why? Do I have to activate something?

Answer 1

You need to set JOB_QUEUE_PROCESSES to a positive number in order to enable database jobs to run. JOB_QUEUE_PROCESSES controls the number of jobs that are allowed to run simultaneously so if you set the parameter to 1, only 1 job could run at a time. If you're going to be creating multiple materialized views that you want to refresh at approximately the same time or if you are potentially going to be creating additional jobs, you may want to set this parameter to a value greater than 0.