Re: Oracle job scheduler problem

Steve wrote:
>
> Found solution to problem.
>
> Need to issue commit after dbms_job.summit;
>
> "Steve" <no_at_anon> wrote in message
> news:1017144474.15583.0.nnrp-01.c2d92da9_at_news.demon.co.uk...
> > In a nutshell jobs are not running until I adjust number of
> > job_queue_processes using alter system.
> >
> > Details:
> >
> > I have the following parameters set:
> >
> > job_queue_processes = 3
> > job_queue_interval = 60
> >
> > Start the database, then in a SQL*Plus session I issue a job as follows:
> >
> > sql> var jn number;
> > sql> execute dbms_job.submit(:jn, 'DO_STATS;', sysdate + 2 / 1440,
> 'sysdate
> > + 2 / 1440');
> >
> > I won't actually have do_stats running every two minutes, this is just for
> > test purposes at the moment.
> >
> > Selecting from dba_jobs I get
> >
> > JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC FAILURES
> WHAT
> > INTERVAL
> > ---- --------- -------- --------- -------- --------- -------- ---------- -
> --
> > ----------------- --------------------
> > 41 26-MAR-02 11:59:59
> > DO_STATS; sysdate + 2 / 1440
> >
> > Current Date Time is
> >
> > TO_CHAR(SYSDATE,'DD-
> > --------------------
> > 26-mar-2002 11:58:33
> >
> > I wait for a few minutes:
> >
> > TO_CHAR(SYSDATE,'DD-
> > --------------------
> > 26-mar-2002 12:01:59
> >
> > Check dba_jobs again
> >
> > JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC FAILURES
> > WHAT INTERVAL
> > ----- --------- -------- --------- -------- --------- -------- ----------
> --
> > ------------------ --------------------
> > 41 26-MAR-02 11:59:59
> > DO_STATS; sysdate + 2 / 1440
> >
> > Job has not run.
> >
> > Log in as System and issue
> >
> > sql> alter system set job_queue_processes = 4
> >
> > Query dba_jobs again
> >
> > JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC FAILURES
> > WHAT INTERVAL
> > ----- --------- -------- --------- -------- --------- -------- ----------
> --
> > ------------------ --------------------
> > 41 26-MAR-02 12:07:18 26-MAR-02 12:09:18 0
> > DO_STATS; sysdate + 2 / 1440
> >
> > Job has run and continues to do so every two minutes.
> >
> > Why did none of the initial 3 SNP processes pick the job up, why did I
> have
> > to create another SNP process?
> >
> > Regards
> >
> >

Some additional information about jobs at http://www.jlcomp.demon.co.uk/faq/dbms_job.html

The commit concept is a wonderful feature - you can use a trigger to submit a job when a row is created - but the job will not be run if the user issues a rollback

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."