Re: DBMS_JOB Package
Date: 1995/09/15
Message-ID: <legion-1509952237030001_at_dial-ip-ttysb.inet-serv.com>
In article <1995Sep12.230140.28462_at_lamont.ldgo.columbia.edu>, mvergara_at_sctcorp.com (Michael P. Vergara) wrote:
> I saw a reference to this package on this group, and it sounds like
> something I've been looking for. However, I can find no
> documentation in the procedure. Does anyone have ...
> a) any idea where it's documented?
> b) experience using it?
>
> I'm interested in hearing anything you have to say.
>
Thank's to Andrew McPherson I have the following information about the Job Queue Facility. Hopefully this information is helpfull.
VOUG Newsletter No 18: June 1995
Published by ANZORA-VIC, GPO Box 1361M, Melbourne, Vic, 3001 FREE TO FINANCIAL MEMBERS Oracle Job Queue Facility
By Andrew McPherson
Overview
Oracle 7.1.3 includes an undocumented facility to run stored procedures, "jobs", at specified times and frequencies. The only "documentation" available for this facility is contained in the comments in dbmsjob.sql, the script that creates the dbms_job package. These comments state that the facility has the following features:
- Runs user-defined routines from background processes (or directly in the user's process).
- Runs the jobs at user defined times (or reasonably soon afterwards).
- Runs a given job repeatedly at user defined intervals (or just once, then it deletes itself).
- Runs the jobs in the same environment they were submitted in (except with the user's default roles and privileges)
- Reports errors, and does exponential backoff.
- Allows the user to identify and administer these jobs.
All of these facilities, except item 5, are adequately provided by the facility. There is a deficiency in the way in which errors are reported, in that they are included into the database alert file and/or into a trace file, which makes them difficult to obtain in an automatic fashion. There is also no record of when jobs were run (or how frequently), in fact it is only possible to deduce if a job has been run by seeing if its next scheduled execution time has been updated.
This document gives a description of the actions that must be performed in order to use the facility and an explanation, based on observation, of its workings and usage. Much of the information included here has been "deduced" by (attempting) to run it.
Installation
The following actions must have been performed before you are able to use the facility:
- Ensure that the necessary objects are created. This is normally done if you run the catproc.sql script, which is normally run during an installation that includes the procedural option, or during a database update.
- Include the job parameters into your init.ora file. These parameters are: job_queue_processes=n, where n is the number of concurrent background processes permitted. Valid values range from 0 to 9. Default value is 0.
Job_queue_interval=n, where n is the interval in seconds to check the
job queue. Valid values range from 1 to 3600 (1 second to 60 minutes).
Default value is 60.
job_queue_keep_connections=true/false, determines whether or not the
background process remains active after running a job. Default value is
false.
The dbmsjob.sql procedure run by catproc.sql, grants execution access
to the DBMS_JOB package to public. As there are no system privileges
associated with running jobs, if you require to restrict access to this
facility, then you will need to create a role, and grant that role to
authorised users. The DBMS_IJOB package is intended for use by DBA's
only
Operation
The actual operation of the facility is not described in the comments, but you will observe when you enable the facility that the background processes that are started are all named with an SNPn prefix. Chapter 8 of the Oracle7 Server Documentation Addendum Release 7.1, describes the Snapshot Refresh facility. The mechanism's and parameters used for Snapshot Refresh bare a remarkable resemblance to those used for the Job Queue facility. Even more remarkable, is that the view USER_REFRESH includes a column JOB, that is designated as "reserved for future use".
It is reasonable to assume therefore, that the Job Queue facility is simply a different interface to (or may in fact be) the underlying facility that has been built by Oracle to support periodic Snapshot Refresh.
Observation reveals that these SNPn background processes, under UNIX at least, are an instance of the oracle executable. The SNPn processes "wake up" at periodic intervals (as set by job_queue_interval), and examine the job queue for anything due (or overdue) to run. They establish a session under the username of the submitter of the job, adjust the environment, in so far as that is possible by means of ALTER SESSION statements and invoke the specified procedure.
Each job submitted can have an optional INTERVAL, which if present, is an expression that is used to compute the date/time when this procedure should be executed again. This expression is evaluated before the procedure is executed, and if it returns null, this will be the last execution of the procedure and it will be deleted from the job queue on successful completion. (An oversight in this facility is the lack of a processed jobs log).
If the job cannot be initiated (missing procedure, syntax error, etc.) or fails during its execution (any unhandled exceptions), then the job is automatically resubmitted by the background procedures at a future time. This time is calculated exponentially, based on the number of attempts (failures) to run the job. To avoid this problem, all procedures should have a general purpose exception handler that sets the job status to "broken", if any unanticipated exceptions occur. DO BE CAREFUL, the background processes don't like errors, I have noticed that some unhandled exceptions can cause the background process executing the procedure to "hang"and no longer run any jobs, so I recommend testing all jobs using the "run" procedure, before leaving them to the background processes.
A packaged procedure, DBMS_JOB, provides the only means of manipulating jobs. It contains procedures to submit, change, remove and run a job. An additional package DBMS_IJOB, is also created, but is not publicly available. The exact nature of DBMS_IJOB is not mentioned in the comments, the 'I' however, would appear to mean Internal, as it run's as SYS, and can be used by DBA's to manipulate other user's jobs.
The system views DBA_JOBS, USER_JOBS and ALL_JOBS are provided to interrogate the job queue. The view DBA_JOBS_RUNNING provides the only method of viewing running jobs, although the columns THIS_DATE and THIS_SEC are only present in the job queue views when the job is running.
Submitting a Job
There are two procedures available in DBMS_JOB for submitting jobs, submit, which will allocate (and return) a new job number, and isubmit, in which the user must specify the job number to be used (this method is not recommended for normal use, as job numbers must be unique). The definitions of these procedures are:
PROCEDURE submit ( job OUT BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE DEFAULT sysdate, interval IN VARCHAR2 DEFAULT 'null', no_parse IN BOOLEAN DEFAULT false);
PROCEDURE isubmit ( job IN BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE DEFAULT sysdate, interval IN VARCHAR2 DEFAULT 'null', no_parse IN BOOLEAN DEFAULT false);
Parameters:
job The unique identifier for this job. When using submit, the procedure obtains a new job number from the sequence sys.jobseq and returns it to the calling procedure.
what The PL/SQL procedure to execute, which must be terminated with a trailing semi-colon. This may only be a single call to a stored procedure. The procedure may be passed as many hardcoded parameters as desired, and the following special values may also be passed as parameters:
job an IN parameter, the number of the current job.
next_date an IN/OUT parameter, the date/time of the next scheduled
execution of this job. This is not the date/time of the present
execution, but the next. The job itself can change this during its
execution.
broken an IN/OUT parameter, it is always FALSE on entry, as broken
jobs are not run. It provides the opportunity for the job itself
to determine that it is "broken".
For example:
'myproc( ''10-JAN-82'', next_date, broken);'
'scott.emppackage.give_raise( ''JENKINS'', 30000.00);'
'dbms_job.remove( job);'
next_date The date at which the job should next be automatically run by the system, assuming there are background processes available to run it. The actual start time of the job depends on a number of things: the job_queue_interval, that sets the sleep time of the background processes, the job_queue_processes, which specifies the maximum number of concurrent processes and how many other jobs are due to run at the same time.
interval This must be a date function, which is evaluated immediately before the job starts executing. It must evaluate either to null or a time in the future. If the job completes successfully, this new date then determines the next scheduled execution of this job. If it evaluates to null, and the job completes successfully, then the job will be automatically deleted from the queue. This parameter is evaluated by the statement, SELECT interval INTO next_date FROM dual;
For example:
'NEXT_DAY(sysdate, "TUESDAY")'
execute every Tuesday,
'sysdate + 7'
execute once a week,
'sysdate + (1 / 24)'
execute every hour
no_parse When TRUE, the what parameter is not parsed until execution time. This is useful if jobs and procedures are being loaded from batch files, and the procedure to be executed may not have been loaded yet. In normal circumstances it should be left to its default.
Making Changes to a Submitted Job
Every aspect of a submitted job can be changed. DBMS_JOB provides both a general change procedure, and procedures for changing an individual parameter, provided that you are the user who submitted the job. The "environment" (NLS and session settings) in which a job executes cannot be changed directly, but is automatically set whenever the "what" parameter is changed.
The general change procedure allows any parameter or combination of parameters to be changed. If any value is null, then that parameter is not changed. This means that you cannot use this procedure to set "interval" to null. The definition for this procedure is:
PROCEDURE change ( job IN BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE, interval IN VARCHAR2); The "what" procedure changes the procedure (and/or its parameters) that will be executed by the job. It also sets the "environment" of the job to be the same as the " environment" of the session executing the procedure. The definition for this procedure is:
PROCEDURE what ( job IN BINARY_INTEGER, what IN VARCHAR2 );
The "next_date" procedure changes the date and time at which the job will next be executed. This must be in the future. The definition for this procedure is:
PROCEDURE next_date ( job IN BINARY_INTEGER, next_date IN DATE );
The "interval" procedure changes the formula used by the job queue to determine at the beginning of the job, when it should be run again. Setting this value to null, causes the job to be deleted after its next execution. The definition for this procedure is:
PROCEDURE interval ( job IN BINARY_INTEGER, interval IN VARCHAR2 );
This procedure can be used to suspend the execution of a job by marking it as "broken", or it can be used to reset the state, and specify a new "next_date" for its next execution (if unspecified, then it will be submitted immediately).
PROCEDURE broken ( job IN BINARY_INTEGER, broken IN BOOLEAN, next_date IN DATE DEFAULT SYSDATE ); Removing a Job from the Queue
DBMS_JOB provides a procedure that allows a User to remove any jobs they have submitted. A DBA can use the same procedure in SYS.DBMS_IJOB, to remove any job that has been submitted. The definition of this procedure is:
PROCEDURE remove ( job IN BINARY_INTEGER );
Forcing a Job to run now
DBMS_JOB provides a procedure that forces a specific job to be run immediately, even if it is "broken". This procedure uses the current user session to process the procedure, not a background process, so it will adjust your sessions's "environment" to that required for the job (and leave it that way!!) and perform a commit. A DBA can make use of the same procedure in SYS.DBMS_IJOB, to run any job (effectively, this is the procedure run by a background process). If any errors are encountered during the execution, the complete errorstack is dumped to a trace file and to the alert file.
BE AWARE: Having used this procedure your current session is no longer in the same state it was, a commit will have occurred, your "environment" may have been changed and any referenced packages will have been re-initialised, plus whatever the job itself may have done to your session.
PROCEDURE run ( job IN BINARY_INTEGER );
Other Functions
DBMS_JOB provides two other functions, "user_export", which can be used to create a procedure call that when executed would resubmit the job, and "check_privs", which is entirely undocumented.
The "user_export" procedure is obviously intended for use by either export or to allow a user-written job export procedure and is defined as:
PROCEDURE user_export ( job IN BINARY_INTEGER, mycall IN OUT VARCHAR2);
The "check_privs" procedure returns no information, simply returning success if the current user is the owner of the procedure. The procedure will generate error 23421, if the user is not the owner of the job, or if the job does not exist. I have not yet found a use for this procedure.
PROCEDURE check_privs ( job IN BINARY_INTEGER );
Job Queue Views
The DBA_JOBS, USER_JOBS and ALL_JOBS views (ALL_JOBS is a synonym for USER_JOBS), can be used to display the complete details of jobs in the queue. The DBA_JOBS_RUNNING view can also be used by DBA's to only report on Jobs currently running. Within limits a user can see if any jobs are running by selecting from all rows from USER_JOBS where THIS_DATE is not null. THIS_DATE is normally null, except when the job is actually running.
The views contain three USER columns, the logon user, the privileges user and the schema owner user. At the present time all these users are the same, the user who submits the job. This is either intended for future use, or is used by the Refresh Snapshots facility.
The views also contain separate date and time columns. This is for convienence only, the date columns are standard Oracle DATE fields, while the time columns are to_char's used to display the time component.
The information available in the views is:
JOB Identifier of the job. Neither import/export not repeated executions will change it.
LOG_USER The USER who was logged in when the job was submitted.
PRIV_USER The USER who's default privileges apply to this job.
SCHEMA_USER The USER who owns the objects accessed by the procedure.
LAST_DATE The Date that this job was last successfully executed.
LAST_SEC The Time that this job was last successfully executed.
THIS_DATE The Date this job started executing.
THIS_SEC The Time this job started executing.
NEXT_DATE The Date this job will next be executed.
NEXT_SEC The Time this job will next be executed.
TOTAL_TIME The Total Elapsed time spent on this job by the system, in seconds.
BROKEN Y, if the job is broken, N otherwise. The system will not execute broken jobs.
FAILURES The number of times this job has been started, and not finished successfully, since its last successful execution.
INTERVAL The date function that is evaluated at the start of execution to determine what the NEXT_DATE should become.
WHAT The body of the anonymous PL/SQL block that this job executes.
NLS_ENV Alter Session parameters describing the NLS environment of the job.
MISC_ENV A versioned raw maintained by the kernel. Contains information for other session parameters.
CURRENT_SESSION_LABEL Trusted Oracle.
CLEARANCE_HI Trusted Oracle.
CLEARANCE_LO Trusted Oracle. Received on Fri Sep 15 1995 - 00:00:00 CEST