- Integrated Systems
- About Us
- Integrated Systems
- About Us
08-05-2003 11:24 AM
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?.
08-05-2003 11:52 AM
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"
Owner of ISN Corporation
08-05-2003 12:03 PM
crontab -e oracle
08-05-2003 01:15 PM
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.
Let me know if this works for you, or if you have any questions.
08-05-2003 03:51 PM
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
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
The following is an example of a DBMS_JOB.SUBMIT:
SQL> EXECUTE dbms_job.submit(:x,'procedure_name;',sysdate,'sysdate+1')
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.
08-05-2003 10:22 PM
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 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 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) 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:
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)');
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
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
This supplements the above replies.
Hope this helps!