Home » SQL & PL/SQL » SQL & PL/SQL » DBMS JOB SCHEDULING
DBMS JOB SCHEDULING [message #199410] Tue, 24 October 2006 02:03 Go to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Hi,

I am running 10.2.0.2.0 Standard Edition on RHEL4. I need backward compatability with 9i and this is why I am not using the scheduler.

I would like to run a certain job at 2 minutes past the hour every hour but am having trouble achieving this. Any ideas?

Here is the code which is working but runs the job every hour on the hour:-

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'delete from sys.aud$;commit;', trunc(SYSDATE+1/24,'HH24'), 'trunc(SYSDATE+1/24,''HH24'')', TRUE, :instno);
commit;
end;
/

Thanks in advance,

Ken.
Re: DBMS JOB SCHEDULING [message #199415 is a reply to message #199410] Tue, 24 October 2006 02:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
trunc(sysdate, 'hh24') rounds back to this hour and zero minutes.
If you add 1/24 to that, you will get the next hour plus zero minutes.
Solution: add 2 minutes to it:
 dbms_job.submit(:jobno, 'delete from sys.aud$;commit;', trunc(SYSDATE+1/24,'HH24') + 2/(24*60),...
Re: DBMS JOB SCHEDULING [message #199419 is a reply to message #199415] Tue, 24 October 2006 03:14 Go to previous messageGo to next message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Frank,
Thanks for the swift reply.
But there is an isue - what the above will do is to schedule the first job to run at 2 minutes past the next hour but then will default again to on the hour, I believe.
What are your thoughts?
Thanks again,
Ken.
Re: DBMS JOB SCHEDULING [message #199432 is a reply to message #199419] Tue, 24 October 2006 05:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, you could be right. I didn't check the docs for the meaning of each of the parameters; I thought to remember that the first date was the 'next_run' and the second the 'first_run'

*checking docs*

Yep, you're right, the docs state it clearly:
DBMS_JOB.SUBMIT ( 
   job       OUT BINARY_INTEGER,
   what      IN  VARCHAR2,
   next_date IN  DATE DEFAULT sysdate,
   interval  IN  VARCHAR2 DEFAULT 'null',
   no_parse  IN  BOOLEAN DEFAULT FALSE,
   instance  IN  BINARY_INTEGER DEFAULT any_instance,
   force     IN  BOOLEAN DEFAULT FALSE);

Parameters
Table 20-2 SUBMIT Procedure Parameters
Parameter 	Description

job             Number of the job being run.
what            PL/SQL procedure to run.
next_date       Next date when the job will be run.
interval        Date function that calculates the next time to run the job.
                The default is NULL. This must evaluate to a either
                a future point in time or NULL.


On second thought, I DID supply a correct answer, for I didn't provide the interval parameter. You should have been able to complete/alter the call using my reply..

[Updated on: Tue, 24 October 2006 05:02]

Report message to a moderator

Re: DBMS JOB SCHEDULING [message #199455 is a reply to message #199432] Tue, 24 October 2006 08:04 Go to previous message
KenJ
Messages: 69
Registered: July 2006
Location: London
Member
Thanks Frank. It works.

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'delete from sys.aud$;commit;', trunc(SYSDATE+1/24,'HH24')+ 2/(24*60), 'trunc(SYSDATE+1/24,''HH24'')+ 2/(24*60)', TRUE, :instno);
commit;
end;
/
Previous Topic: ORA-01403: no data found
Next Topic: regarding query grouping
Goto Forum:
  


Current Time: Fri Dec 09 15:27:09 CST 2016

Total time taken to generate the page: 0.22014 seconds