DBMS_JOB

[bsatheesh]

Hi,

I have tried the following:

exec DBMS_JOB.SUBMIT(:jobno,'proc;',sysdate,'sysdate+1/5600');

and it get executed successfully and proc also has been executed once. In user_jobs table there is entry for this job and next_date value also been set.

But, this procedure has not been executed next time.

I have tried to start the job by

exec DBMS_JOB.run(jobno); and
exec DBMS_JOB.interval(jobno,'sysdate+1/5600');

but there is no success.

please suggest the solution to run thejob in the interval.

thanks,
satheesh.

[akkerend]

The job did run one time?
LAST_DATE in DBA_JOBS is not NULL for jobno?
If it is NULL, is JOB_QUEUE_PROCESSES in your init.ora not 0?
FAILURES in DBA_JOBS is 0?
BROKEN in DBA_JOBS is 'N'?

Maybe there are other jobs scheduled before this job or, one or more SNP processes hang.

[bsatheesh]

Thank you for your reply.

The job did run one time?

Yes

LAST_DATE in DBA_JOBS is not NULL for jobno?

yes. It has last execution time

If it is NULL, is JOB_QUEUE_PROCESSES in your init.ora not 0?

There is no such entry in init.ora file.

FAILURES in DBA_JOBS is 0?

Yes.

BROKEN in DBA_JOBS is 'N'?

It is 'N'.

Also, this is the only entry in dba_jobs and user_jobs. Also, the next_date field is set ahead 13 second( approx.) of last_date.

What else to be run?

thanks ,
satheesh

[akkerend]

Check the value of job_queue_parameters in v$parameter.
If it is 0, add it to your init.ora and set it to 3. It is the number of SNP (job) processes. Even if you don't have many jobs, 1 or 2 are mostly not enough. Jobs can hang and different jobs have often to be run at the same time.

[bsatheesh]

The parameters in v$PARAMETER are set like:

job_queue_parameter 0
job_keep_connection FALSE


I have added these two parameters in init.ora file with 3,TRUE, but there is no success.( I have removed the old job and recreated it )

[akkerend]

You shut down and restarted the database after the change of the init.ora?

Does the job not run at all or with a larger interval?