oracle job

[tduzen]

version 8.1.7,a job did not start at the scheduled time. What can I do?

[abhaysk]

post the code.

[aarroyob]

Could you tell as the value of the job_queue_processes and job_queue_interval in your database?

[marist89]

Has it worked before?

[tduzen]

DECLARE v_job NUMBER;
BEGIN
DBMS_Job.Submit(v_job,
'BEGIN DBMS_Stats.Gather_Schema_Stats(''MERKEZ''); END;',
to_DATE('29.03.2003 01:30:00','DD.MM.YYYY hH:MI:SS') ,
'Sysdate + 1');
COMMIT;
DBMS_Output.Put_Line('Job: ' || v_job);
END;

There is no problem creating a job. But nothing happen when the time will come. In this database, this is the first job created and there is not any job that runs before.

job_queue_processes:
type : 3
value: 0
ISDEFAULT :true
ISSES :false
ISSYS_MOD: IMMEDIATE
ISMODIFIED :false
ISADJ: FALSE


job_queue_interval:
type : 3
value: 60
ISDEFAULT :true
ISSES :false
ISSYS_MOD: false
ISMODIFIED :false
ISADJ: FALSE

[jmodic]

Originally posted by tduzen
job_queue_processes:
type : 3
value: 0

You don't have any SNP process running, so there is nothing to pick your job out of the job_queue and execute it. You must set parameter JOB_QUEUE_PROCESS to at least 1 in order to be able to execute database jobs. And you have to bounce the database after you change this init parameter, of course.

[tduzen]

thank you very much, I will try this immediately...

[slimdave]

DECLARE v_job NUMBER;
BEGIN
DBMS_Job.Submit(v_job,
'BEGIN DBMS_Stats.Gather_Schema_Stats(''MERKEZ''); END;',
to_DATE('29.03.2003 01:30:00','DD.MM.YYYY hH:MI:SS') ,
'Sysdate + 1');
COMMIT;
DBMS_Output.Put_Line('Job: ' || v_job);
END;

If your intention is that the analyze job should start at 01:30:00 every day, then the bolded selection should read ...

'Trunc(sysdate)+1+(1.5/24)'

... otherwise the job will "creep" forward. This is because the expression is evaluated after the job has executed, not before.