20 DBMS_JOB → DBMS_JOB subprograms schedule and manage jobs in the job queue. See Also: For more information on … the DBMS_JOB package and the job queue, see Oracle9i Database Administrator's Guide This chapter … discusses the following topics: Requirements Using the DBMS_JOB Package with Oracle Real Application Clusters Summary of DBMS_JOB
Summary of DBMS_JOB Subprograms → Table 20-1 DBMS_JOB Package Subprograms Subprogram Description SUBMIT Procedure Submits a new job
The DBMS_JOB Package → To schedule and manage jobs in the job queue, use the procedures in the DBMS_JOB package. There are … procedures can use the job queue. The following are procedures of the DBMS_JOB package. They are … for the DBMS_JOB package, and for information about other options available when using the DBMS_JOB
Parameters → Table 20-5 WHAT Procedure Parameters Parameter Description job Number of the job being run. what PL/SQL procedure to run. Some legal values of what (assuming the routines exist) are: 'myproc( ''10-JAN-82'', next_date, broken);' 'scott.emppackage.give_raise( ''JENKINS'', 30000.00);' 'dbms_job.remove(job);'
SUBMIT Procedure → This procedure submits a new job. It chooses the job from the sequence sys. jobseq.
Usage Notes → If the job completes successfully, then this new date is placed in next_date. interval is evaluated by plugging it into the statement select interval into next_date from dual; The interval parameter must evaluate to a time in the future. Legal intervals include: Interval Description 'sysdate + 7' Run once a week. 'next_day(sysdate,''TUESDAY'')' Run once every Tuesday. '' Run only once. If interval
Parameters → Table 20-11 USER_EXPORT Procedure Parameter Parameter Description job Number of the job being run. mycall Text of a call to recreate the given job.
Parameters → Table 20-12 USER_EXPORT Procedure Parameters Parameter Description job Number of the job being run. mycall Text of a call to re-create a given job. myinst Text of a call to alter instance affinity. Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. Home Book List Contents Index Master Index Feedback
Usage Notes → The parameters instance and force are added for job queue affinity. Job queue affinity gives users the ability to indicate whether a particular instance or any instance can run a submitted job.
Example → EXECUTE DBMS_JOB.REMOVE(14144);
Syntax → DBMS_JOB.CHANGE ( job IN BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE, interval IN VARCHAR2, instance IN BINARY_INTEGER DEFAULT NULL, force IN BOOLEAN DEFAULT FALSE);
Example → EXECUTE DBMS_JOB.CHANGE(14144, , , 'sysdate+3');
NEXT_DATE Procedure → This procedure changes when an existing job next runs.
Example → EXECUTE DBMS_JOB.RUN(14144); Caution: This reinitializes the current session's packages.
USER_EXPORT Procedure → This procedure produces the text of a call to re-create the given job.
Usage Notes → The parameters instance and force are added for job queue affinity. Job queue affinity gives users the ability to indicate whether a particular instance or any instance can run a submitted job. If the parameters what, next_date, or interval are NULL, then leave that value as it is.
INTERVAL Procedure → This procedure changes how often a job runs.
RUN Procedure → This procedure runs job JOB now. It runs it even if it is broken. Running the job recomputes next_date. See view user_jobs.
Syntax → DBMS_JOB.USER_EXPORT ( job IN BINARY_INTEGER, mycall IN OUT VARCHAR2);
Using the DBMS_JOB Package with Oracle Real Application Clusters → For this example, a constant in DBMS_JOB indicates no mapping among jobs and instances; that is