Now, the jobs in the queue are getting executed after I changed the job_queue_interval to 5secs. But, I have one more problem. When the job gets executed it gives error -
ORA-12012: error on auto execute of job 15
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 386
ORA-06512: at "SYS.DBMS_IREFRESH", line 450
ORA-06512: at "SYS.DBMS_REFRESH", line 182
ORA-06512: at line 1
After it got executed a number of times, the job was marked as BROKEN. Does anyone know why I am getting that invalid cursor problem. It works fine if I do dbms_job.run(15).
BTW, how many times does a job get executed with error before it is marked as broken?
What is your JOB_QUEUE_PROCESSES setting? What does this job of yours perform, how long does it take to finish? What is the interval of your job reexecution (not the job queue interval, but the interval of this particullar job)?
About the marking a job as broken by the system: Oracle tries to repeat the job if it fails up to 16 attempts, doubling the interval between each subsequent attemp. Let's say you set jor job to repeat each minute. If the job fails, Oracle will try to repeat it after one munte. If it fails again it will reatempt it after 2 minutes, then after 4 minutes and so on. If it fails 16 times consecutively then oracle marks this job as broken and will no longer attempt to run it.
job_queue_interval=5 (5 seconds)
My job is supposed to be executed every one minute. The job is supposed to take 1 or 2 seconds to complete. It is a simple snapshot refresh job on a table with 5 records.
Doing it manually ie dbms_job.run(job#) is fine. Takes 1 sec to complete.
I also notice that now my db is generating 10Mb of redologs every 2-3 minutes. Strange!