Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Do you see an entry in DBA_JOBS to refresh the materialized view? If so, what are the NEXT_DATE, NEXT_SEC, LAST_DATE, LAST_SEC, and FAILURES values? What is the initialization parameter JOB_QUEUE_PROCESSES set to? How many other jobs do you have in DBA_JOBS? –  Justin Cave Nov 14 '11 at 18:40
    
Yes, there the refresh is in the jobs. Next date, next sec is in the past, last date and failure are empty. JOB_QUEUE_PROCESSES is set to 0. No other jobs are in dba_jobs. Do I have to enable JOB_QUEUE_PROCESS? Is that enought? –  Gianluca Frezza Nov 15 '11 at 9:16

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
It's generally safe to set this pretty high; at my last site, IIRC we had it to 70. –  Gaius Nov 16 '11 at 9:45
    
Thanks, I set JOB_QUEUE_PROCESS to 1 and magically the materialized view has been updated at the desired time. –  Gianluca Frezza Nov 16 '11 at 13:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.