Use the DBMS_JOB package to define instance affinity.
Controlling Job Execution with Instance Affinity
When using OPS for batch jobs, there are times when your job only needs to run using a single instance. For example, perhaps you have a job
that gathers sales statistics for a particular district office, whose data is all stored within a single database instance. Limiting the job
to run on one instance can cut overhead costs because you can prevent the job from interacting with other instances that do not have any job
data. Limiting a job to a single instance is called instance affinity and is a new feature in Oracle8i. Do not confuse this with disk affinity, which is another feature of OPS that we will discuss in the next lesson.
Two Initialization Parameters
Before you can run a batch job using 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).
Using DBMS_JOB to control Job Execution
The DBMS_JOB package has been superseded by the DBMS_SCHEDULER package. In particular, if you are administering jobs to
manage system load, you should consider disabling DBMS_JOB by revoking the package execution privilege for users.
The DBMS_JOB package schedules and manages jobs in the job queue.
No specific system privileges are required to use DBMS_JOB. No system privileges are available to manage DBMS_JOB. Jobs cannot be altered or deleted other than jobs owned by the user. This is true for all users including those users granted DBA privileges.
You can execute procedures that are owned by the user or for which the user is explicitly granted EXECUTE. However, procedures for which the user is granted the execute privilege through roles cannot be executed.
Note that, once a job is started and running, there is no easy way to stop the job.
Navigate through the following series of images to see the steps in creating and running a job using DBMS_JOB to control job execution.
The following parameters can be used with the 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.