Thanks for the question, Sridevi.
Asked: November 26, 2001 - 3:20 pm UTC
Last updated: November 27, 2003 - 11:46 am UTC
Version: Oracle 7.x
Viewed 1000+ times
You Asked
In Oracle 7.x, I have scheduled a job via dbms_job in oracle.
In the init.ora file I have set the parameters :
job_queue_processes = 5
job_queue_interval = 300
Then I have submitted the job using dbms_job.submit.
I have scheduled the job to run every day at 1:30 pm.
The syntax being:
dbms_job.submit(p_job_number, 'myproc', sysdate, trunc(sysdate)+13.5/24, false);
The job runs as scheduled at 1:30 pm. But from 1:30 pm onwards it startes writing one file to the archiver log every minute or so. However it does not run my procedure every minute.
I have tried this even with a simple null procedure.
In other words, I have tried this with a procedure like this:
Procedure myproc is
Begin
null;
End;
It still writes to the archiver, after it runs once on the scheduled time. And continues to write to the archiver every minute or so. I have even tried this on a just started database with no writes/reads
being done to it from any logins. So the culprit is the job.
This writing to the archiver stops if I kill the job using dbms_job.remove or I shutdown the database.
Why does this happen? How can i prevent these frequent entries into the archiver log.
and Tom said...
what do you mean by "writing one file to the archiver log every minute of so"???
You do realize that you have somewhat of an infinite loop going there.
scott@ORA815.US.ORACLE.COM> l
1* alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'
scott@ORA815.US.ORACLE.COM> select trunc(sysdate)+13.5/24 from dual;
TRUNC(SYSDATE)+13.5/
--------------------
26-nov-2001 13:30:00
you do realize that INTERVAL should be a string -- that should be
dbms_job.submit
(p_job_number, 'myproc', sysdate, 'trunc(sysdate)+13.5/24', false);
^^^ ^^^
And that it probably should actually be:
dbms_job.submit
(p_job_number, 'myproc', sysdate, 'trunc(sysdate)+1+13.5/24', false);
^^^ ^^^ ^^^
if you want it to run every DAY at 1:30pm. You constantly reschedule it for 1:30pm TODAY. Everytime we run the job, it FAILS. Consider (if you schedule this job AFTER 1:30 in the afternoon)
scott@ORA815.US.ORACLE.COM> exec dbms_job.submit( :n, 'null;', sysdate, 'trunc(sysdate)+13.5/24', false );
BEGIN dbms_job.submit( :n, 'null;', sysdate, 'trunc(sysdate)+13.5/24', false ); END;
*
ERROR at line 1:
ORA-23420: interval must evaluate to a time in the future
ORA-06512: at "SYS.DBMS_JOB", line 50
ORA-06512: at "SYS.DBMS_JOB", line 138
ORA-06512: at line 1
You need to add one to the date.
What I believe is happening (and your alert log will confirm) is that we are trying to run the job -- but we cannot since the NEXT evaluates to a time in the PAST (until midnight of course -- then it works, all will be quiet until 1:30pm at which point the whole cycle begins again).
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment