dbms_jobs

[robert_177]

I want to schedule a procedure to run every 5 mins in Oracle

DBMS_JOB.SUBMIT (:v_jobnum, 'my_procedure;', trunc(sysdate) + 1, 'sysdate + 1/288');

Is this the right way?
and also what is :v_jobnum supposed to be?.

[Steven E. Protter]

You are trying to re-invent cron.

Script your job and then add it to the cron schedule.

5,10,15,20,25,30,25,40,45,50,55 * * * * jobname 2>&1


contents of jobname

su - oracle -c "script running your procedure"


SEP

[doug mielke]

I require the DBAs to put their Oracle Jobs under user Oracle's cron, so they do not clutter up root's. ( they can also edit their cron with out root access)

crontab -e oracle

[Brian Crabtree]

v_jobnum is an "OUT" variable, which means that it passes the job number back to your application when it is complete.

The following should work for what you want to do. Please note that because Oracle only schedules at the end of the job, the job will eventually drift. This is the reason for the extra code.

declare
jobout binary_integer;
begin
dbms_job.submit(jobout,'my_procedure;',trunc(SYSDATE+(5/1440),'MI')-(mod(to_char(sysdate,'MI'),5)/1440),'trunc(SYSDATE+(5/1440),''MI'')-(mod(to_char(sysdate,''MI''),5)/1440)');
end;
/

Let me know if this works for you, or if you have any questions.

Thanks,

Brian

[twang]

To submit a job to the job queue.

DBMS_JOB.SUBMIT(job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE,
interval IN VARCHAR2,
no_parse IN BOOLEAN)


Job: When a job is created a job number is
assigned to it. This job number is
unique. You must use the job number
whenever you alter or remove the job.
As long as the job exists, the number
will remain the same.

What: The PL/SQL code you wish to execute. It is
usually a call to a stored procedure, which
can have any number of parameters. Use two
single quotes around strings and a
semicolon at the end of the job definition.

Next_date: The date when the job will execute. Default
is SYSDATE.

Interval: A function that calculates the next time the
job is to execute. Default is NULL.

No_parse: If TRUE, Oracle parses the code the first
time the job is executed. If FALSE,
the job is parsed when submitted. Default is
FALSE.


The following is an example of a DBMS_JOB.SUBMIT:

SQL> EXECUTE dbms_job.submit(:x,'procedure_name;',sysdate,'sysdate+1')
SQL> COMMIT;

When a job is submitted to the job queue, it runs automatically depending
on the NEXT_DATE and INTERVAL parameters. Jobs will not run automatically
when submitted on newly created databases that have not been bounced.

[Yogeeraj_1]

Hi,

First of all, in order for DBMS_JOB to function correctly, you need to do a little setup in the database.
There are two init.ora parameters that you need to set:


JOB_QUEUE_PROCESSES

JOB_QUEUE_PROCESSES specifies the number of SNPn background processes per instance, where n is 0 to 9 followed by A to Z. Job queue processes are used to process requests created by DBMS_JOB. Some job queue requests are created automatically; an example is refresh support for table snapshots. If you wish to have your table snapshots updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.


JOB_QUEUE_INTERVAL

JOB_QUEUE_INTERVAL specifies the interval between wake-ups for the SNPn background processes of the instance. Jobs will only be run every N seconds (where N is the setting for this parameter). So, if you set job_queue_interval = 60 but you have asked that a job be run every 30 seconds -- it will only run once a minute as the queues are only inspected for jobs every 60 seconds. A setting of 1-5 minutes is usually sufficient for this parameter.

To get the 5 minutes interval you would do a:

trunc(sysdate)+(trunc(to_char(sysdate,'sssss')/300)+1)*5/24/60

trunc(sysdate) is MIDNIGHT today (wipe out the time).

Then rest of it was figuring out how many 5 minute intervals had happened today alert (the /300 stuff) and adding 1 more 5 minute interval to it -- so we get nice 1:5, 1:10, 1:15, 1:20, 1:25 and so on.

A simple example would be:

declare
l_job number;
begin
dbms_job.submit(job => l_job,
what => 'my_procedure;',
next-date => trunc(sysdate)+(trunc(to_char(sysdate,'sssss')/300)+1)*5/24/60,
interval => 'trunc(sysdate)+(trunc(to_char(sysdate,'sssss')/300)+1)*5/24/60)');
end;

As for the field "job" (your question about what :v_jobnum supposed to be?)

JOB - A job identifier. It is system-assigned (it is an OUT only parameter). You can use this to query the USER_JOBS or DBA_JOBS views by job ID to see the information about that job. Additionally, some routines such as RUN and REMOVE take the job ID as their only input, to uniquely identify the job to run or be removed.

You can check this by doing the following:
yd@MYDB.MU> desc dbms_job.submit
PROCEDURE dbms_job.submit
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER OUT
WHAT VARCHAR2 IN
NEXT_DATE DATE IN DEFAULT
INTERVAL VARCHAR2 IN DEFAULT
NO_PARSE BOOLEAN IN DEFAULT
INSTANCE BINARY_INTEGER IN DEFAULT
FORCE BOOLEAN IN DEFAULT
yd@MYDB.MU>

This supplements the above replies.

Hope this helps!

Regards
Yogeeraj