Home » SQL & PL/SQL » SQL & PL/SQL » dbms_job problem
dbms_job problem [message #259624] Thu, 16 August 2007 02:34 Go to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

hi,
i am working on 9.2.0.1
i created one procedure as 'birthday' on 15/08/2007.
and created a job using dbms_job package for this procedure as follows

declare
  jobno NUMBER;
BEGIN
  dbms_job.submit(jobno,
                  what      => 'birthday;',
                  next_date => trunc(SYSDATE)+8/24,
                  INTERVAL  => 'trunc(SYSDATE)+1+8/24');
 commit;
END;
/

the job is get created with job-id 430.
the procedure is get executed (i got desired o/p from procedure) but bit early than scheduled.i scheduled it to run on 8.00 am everyday.

today i checked in user_jobs to see at what timing the job was get executed..

select job,log_user,last_date,last_sec,next_date,next_sec,
       total_time,interval
from   user_jobs 
where  job = 430;


JOB LOG_USER LAST_DATE  LAST_SEC NEXT_DATE  NEXT_SEC TOTAL_TIME INTERVAL             
--- -------- ---------- -------- ---------- -------- ---------- ---------------------
430 KCLATTN  16/08/2007 07:16:44 17/08/2007 08:00:00          5 trunc(SYSDATE)+1+8/24


it showing that today the job is executed on 07:16:44 am.
why is this difference ?
according to schedule the job should get executed on 8.00 am.
and one more question-
due to some reason if my server is closed for 1/2 days
or at time of job-execution(in this case 8.00 am) then
what happen to job ? will it get executed after server re-starts?

thanks in advance.

regards,
Ram.
Re: dbms_job problem [message #259702 is a reply to message #259624] Thu, 16 August 2007 05:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Could you look at the Failures column as well.
It is possible that the job failed repeatedly on the 15th and that the first successful execution was at 7:16 on the 16th.
Re: dbms_job problem [message #259992 is a reply to message #259702] Fri, 17 August 2007 01:59 Go to previous messageGo to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

this is what i am getting today
select job,log_user,last_date,last_sec,next_date,next_sec,
       total_time,interval,failures
from   user_jobs
where  job = 430
/

JOB LOG_USER   LAST_DATE  LAST_SEC NEXT_DATE  NEXT_SEC TOTAL_TIME INTERVAL                FAILURES
--- ---------- ---------- -------- ---------- -------- ---------- ----------------------- --------
430 KCLATTN    17/08/2007 08:00:04 18/08/2007 08:00:00          5 trunc(SYSDATE)+1+8/24         0

today job get executed at right time . no problems.
but yesterday it was showing 07:16:44 execution time.
why is this ?
well my server was closed on 15/08/2007...
is that because of this ?

regards,
Ram.
Re: dbms_job problem [message #260051 is a reply to message #259992] Fri, 17 August 2007 04:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The server being shutdown would do it.
When the server starts up, the Jobq process will start, and it will attempt to execute any jobs what are not Broken and have a next run date earlier than the current date.
I'm guessing your server came up at about 7:10 in the morning?
Re: dbms_job problem [message #260290 is a reply to message #260051] Sat, 18 August 2007 02:03 Go to previous message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

got it ,well your guessing is write..
thank you

regards,
Ram.
Previous Topic: Date Query
Next Topic: ORA-12801
Goto Forum:
  


Current Time: Wed Dec 07 05:04:10 CST 2016

Total time taken to generate the page: 0.13209 seconds