More Undocumented Features Oracle Job Queue Facility Running Stored Procedures at Specified Times
More Undocumented Features Oracle Job Queue Facility Running Stored Procedures at Specified Times By Andrew McPherson
Most operating systems' implemented facility has a number of drawbacks: Is the database running when the job starts?; How do I prevent the job running again if something is seriously wrong? Andrew McPherson has uncovered an undocumented feature within Oracle that allows you to answer these questions anD many more.
Beginning with Version 7.1.3, Oracle incorporated a new, but largely undocumented, facility to run stored procedures, or "jobs," at specified times and frequencies as background processes. I can imagine that all those UNIX fans out there will be saying, "So what, we can do that already with cron," and I'm sure in most other environments there are already similar capabilities.
How do I avoid embedding passwords in script files? I'm sure you could think of many other problems you have identified and surmounted in using these facilities.
The beauty of this new facility is that it solves most of the previously mentioned problems in a simple, straight forward manner. The database itself is responsible for invoking the scheduled procedures, so if the database is down at the scheduled time, no matter, the procedure will be started when the database is restarted. Don't want to reveal that critical password? No need, the procedure runs with the privileges of the user that schedules it. Don't want it run again? Simple, set the job status to "broken," it won't be run again until the status is changed.
Sound like everything you always wanted? Well don't get too excited just yet, the facility is undocumented and unsupported at this time.
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 describe the facility as having the following features:
- Runs user-defined routines from background processes or directly in the user's process.
Yes! Scheduled jobs will be executed by dedicated background processes, or you can force an immediate execution within any current session.
- Runs the jobs at user defined times or reasonably soon afterwards.
Yes! When submitting jobs you specify a scheduled start time, and the background processes will execute the job as soon as one is available and awake.
- Runs a given job repeatedly at user -defined intervals or just once, then it deletes itself.
Yes! When submitting jobs you may optionally specify a frequency formula that is used to determine when next the job should be run.
- Runs the jobs in the same environment they were submitted in except with the user's default roles and privileges.
Yes! The environmental options that affect a session., by means of ALTER SESSION, and the National Language Support options are also stored in the job queue entry and the environment is recreated prior to its execution.
- Reports errors, and does exponential backoff.
Well, the errors are reported, but I wouldn't call the alert log and trace files a particularly satisfactory way of reporting errors. Some sort of job log would have been a much better idea.
- Allows the user to identify and administer these jobs.
Yes! By means of the DBMS_JOB package and some simple views, the user is provided with the basic tools to manage scheduled jobs.The only serious drawback with this facility is the absence of any record of when jobs were run, and what their outcome was. In fact, it is only possible to deduce if a job was run by checking if its next scheduled execution time was updated. This limitation can largely be overcome by maintaining a user controlled job log from within the executed processes.
One complication I have encountered with this facility is that it doesn't handle errors well. The background processes have a tendency to "hang up" if the procedure reports an unhandled exception. As a result, I always include a general purpose exception handler that:
The remainder of this article 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.
- logs the problem in my job log,
- sets the job status to "broken,"
- terminates in an orderly fashion
Installation
The following actions must be 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 run during an installation that includes the procedural option, or during a database update.
- Include the job parameters in 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. There are no system privileges associated with running jobs. If you require restricted access to this facility, then a role will need to be created and granted to authorized 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. You will observe, when enabling the facility, the background processes underway are named with an SNPn prefix. Chapter 8 of the "Oracle7 Server Documentation Addendum Release 7.1," describes the Snapshot Refresh facility. The mechanisms and parameters used for Snapshot Refresh bear 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 user name of the user submitting 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. 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, perhaps it's missing a procedure, syntax error, etc., or fails during its execution, 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 (or 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 runs under the SYS userid, and can be used by DBAs 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:
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 at the beginning of a job) which determines 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". It can also be used to reset the job 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:
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 session's "environment" to that required for the job and leave it that way!! It will also 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 error stack is dumped to a trace file and to the alert file. These things will happen when you use this procedure:
Your current session state will have been changed to the scheduled job's state, The package will issue a commit, so any uncommitted data will be stored and outstanding locks will be lost, The "environment" may have been changed, Any referenced packages have been re-initialized, The job you ran may have made additional changes.
Other Functions
DBMS_JOB provides two other functions. They are "user_export" which can be used to create a procedure call that will resubmit the job when executed, and "check_privs", which is entirely undocumented.
The "user_export" procedure is obviously intended for use by either export or 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.
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 DBAs to only report on Jobs currently running. Within limits a user can see if any jobs are running by selecting 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 convenience 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 whose 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. Editor's Note: Readers are encouraged to pick up a copy of the Oracle 7.2 documentation, where this is also documented. It appears in Chapter 7 of the Oracle 7 Server Administrator's Guide.
About the Author Andrew McPherson is Director of Software Engineering, Park Lane, Australia. He has more than 17 years experience with databases, of which the last seven have been with Oracle.
This is a copy of an article published @ http://www.ioug.org/