Lesson 6 | Controlling job execution with instance affinity |
Objective | Use the DBMS_JOB package to define instance affinity. |
DBMS_JOB
package, you must adjust these two initialization parameters:
JOB_QUEUE_PROCESSES
. This parameter defaults to zero. Increase it to one or more to enable jobs to run in the background. It can be a maximum of 36. Setting it higher than one causes the database to spawn multiple symmetric multiprocessing (SMP) background processes that can each run batch jobs simultaneously.JOB_QUEUE_INTERVAL
. This parameter defaults to 60 seconds, which means that the SMP background process wakes up every 60 seconds to start any background jobs scheduled to run at that time. You may leave this as is, or adjust it to be anywhere from 1 to 3,600 seconds (30 minutes). DBMS_JOB
to control job execution.
DBMS_JOB.SUBMIT
procedure.
WHAT
: Required parameter that contains a call to a procedure or some other PL/SQL command to run.NEXT_DATE
: Tells the job queue when to run the job in the background. It defaults to SYSDATE
.INTERVAL
: Optional parameter that tells the job queue how often (in days) to run the job.NO_PARSE
: FALSE by default, meaning the string inside the WHAT
parameter is parsed when you submit the job. You can set it to TRUE
so the string is not parsed.INSTANCE
: Optional parameter that tells the job queue in which instance of the OPS the job runs. If this parameter is left out,the job runs on any instance.FORCE
: Optional Boolean parameter that is set to FALSE
by default. This means that if the specified instance is not available, the job queue may select another instance. DBMS_JOB
in SQL*Plus to run job execution controls.