#

| Chapter 13 Job Scheduling in the Database |

## 13.3 Tips on Using DBMS_JOB

This section discusses several useful tips for using DBMS_JOB.

### 13.3.1 Job Intervals and Date Arithmetic

Job execution intervals are determined by the date expression set by the interval parameter. Getting jobs to run at the desired times can be one of the more confusing aspects of using DBMS_JOB and the job queue. One key to setting the interval correctly is determining which of the following applies to the job:

- Each execution of the job should follow the last by a specific time interval.

- The job should execute on specific dates and times.

Jobs of type 1 usually have relatively simple date arithmetic expressions of the type SYSDATE+N, where N represents the time interval expressed in days. The following table provides examples of these types of intervals.

Action | Interval Value
--- | ---
Execute daily | 'SYSDATE + 1'
Execute hourly | 'SYSDATE + 1/24'
Execute every 10 minutes | 'SYSDATE + 10/1440'
Execute every 30 seconds | 'SYSDATE + 30/86400'
Execute every 7 days | 'SYSDATE + 7'
Do not re-execute and remove job | NULL

Remember that job intervals expressed as shown in the previous table do not guarantee that the next execution will happen at a specific day or time, only that the spacing between executions will be at least that specified. For instance, if a job is first executed at 12:00 p.m. with an interval of SYSDATE + 1, it will be scheduled to execute the next day at 12:00 p.m. However, if a user executes the job manually at 4:00 p.m. using DBMS_JOB.RUN, then it will be rescheduled for execution at 4:00 p.m. the next day. Another possibility is that the database is down or the job queue so busy that the job cannot be executed exactly at the time scheduled. In this case, the job will run as soon as it can, but the execution time will have migrated away from the original submission time due to the later execution. This "drift" in next execution times is characteristic of jobs with simple interval expressions.

Jobs with type 2 execution requirements involve more complex interval date expressions, as seen in the following table.

Action | Interval Value
--- | ---
Every day at 12:00 midnight | 'TRUNC(SYSDATE + 1)'
Every day at 8:00 a.m. | 'TRUNC(SYSDATE + 1) + 8/24'
Every Tuesday at 12:00 noon | 'NEXT_DAY(TRUNC(SYSDATE ), "TUESDAY" ) + 12/24'
First day of the month at midnight | 'TRUNC(LAST_DAY(SYSDATE ) + 1)'
Last day of the quarter at 11:00 p.m. | 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) - 1/24'
Every Monday, Wednesday, and Friday at 9:00 a.m. | NEXT_DAY(SYSDATE, "FRIDAY") ) ) + 9/24'

Specifying intervals like these can get tricky, so be sure that your date arithmetic expression is correct.

I had hoped that another option for evaluating complex job execution intervals would be to write PL/SQL functions with DATE return values that perform the interval calculations. However, my experiments in this area showed that job intervals that call date functions can be successfully submitted to the job queue but may not be properly executed. The SNP processes appeared to have difficulty properly updating the catalog, and jobs became locked in an endless cycle of execution. Perhaps this limitation will be corrected in future releases.

### 13.3.2 Viewing Job Information in the Data Dictionary

Information about jobs in the job queue is available through several data dictionary views (see Table 13.2) created by the catproc.sql script.

Table 13.2: Data Dictionary Views for DBMS_JOB
View Name | Description
--- | ---
DBA_JOBS | All jobs defined to the job queue in this database
DBA_JOBS_RUNNING | All jobs in the database which are currently executing
USER_JOBS | Jobs in the database owned by the current user

Table 13.3 summarizes the various columns in the DBA_JOBS and USER_JOBS views.

Table 13.3: Columns in DBA_JOBS and USER_JOBS Views
Column | Datatype | Description
--- | --- | ---
JOB | NUMBER | Unique identifier of the job
LOG_USER | VARCHAR2(30) | User who submitted the job
PRIV_USER | VARCHAR2(30) | User whose privileges apply to the job
SCHEMA_USER | VARCHAR2(30) | User schema to parse the job under
LAST_DATE | DATE | Last successful execution date
LAST_SEC | VARCHAR2(8) | Hour, minute, and second portion of last_date formatted as HH24:MI:SS
THIS_DATE | DATE | Date current execution began, or NULL if not executing
THIS_SEC | VARCHAR2(8) | Hour, minute, and second portion of this_date formatted as HH24:MI:SS
NEXT_DATE | DATE | Date of next scheduled execution
NEXT_SEC | VARCHAR2(8) | Hour, minute, and second portion of next_date formatted as HH24:MI:SS
TOTAL_TIME | NUMBER | Total elapsed time in seconds for all executions of this job
BROKEN | VARCHAR2(1) | Flag value Y indicates job broken, will not run
INTERVAL | VARCHAR2(200) | Date function used to compute next_date
FAILURES | NUMBER | Number of consecutive unsuccessful executions
WHAT | VARCHAR2(2000) | PL/SQL block executed as the job
CURRENT_SESSION_LABEL | RAW MLSLABEL | Trusted Oracle session label for the job
CLEARANCE_HI | RAW MLSLABEL | Trusted Oracle high clearance for the job
CLEARANCE_LO | RAW MLSLABEL | Trusted Oracle low clearance for the job
NLS_ENV | VARCHAR2(2000) | execution
MISC_ENV | RAW(32) | Other session parameters for job execution

Table 13.4 shows the columns in the DBA_JOBS_RUNNING view.

Table 13.4: Columns in DBA_JOBS_RUNNING View
Column | Datatype | Description
--- | --- | ---
SID | NUMBER | Session ID currently executing the job
JOB | NUMBER | Unique identifier of the job
FAILURES | NUMBER | Number of consecutive unsuccessful executions
LAST_DATE | DATE | Last successful execution date
LAST_SEC | VARCHAR2(8) | Hour, minute, and second portion of last_date formatted as HH24:MI:SS
THIS_DATE | DATE | Date current execution began
THIS_SEC | VARCHAR2(8) | Hour, minute, and second portion of this_date formatted as HH24:MI:SS

The number and size of the columns in DBA_JOBS and USER_JOBS can make them awkward to query interactively. Several examples of useful scripts to run against the job queue dictionary views follow. One thing I usually do is to set my session NLS_DATE_FORMAT to display the full date and time; in this way, I avoid selecting the date and time portions separately. Note that the date columns in these views contain full date values down to the second; the formatted timestamp columns (LAST_SEC, THIS_SEC, NEXT_SEC) are actually derived from them in the views.

This script shows which jobs are currently executing, who owns them, and when they began:

>     /* Filename on companion disk: job2.sql */*
>     col job_definition format a30 word_wrap
>     col username format a15

>     ALTER SESSION SET NLS_DATE_FORMAT='YYYY:MM:DD:HH24:MI:SS';

>     SELECT  jr.job       job_id
>            ,username     username
>            ,jr.this_date start_date
>            ,what         job_definition
>       FROM
>             dba_jobs_running   jr
>            ,dba_jobs           j
>            ,v$session          s
>      WHERE
>             s.sid  = jr.sid
>        AND  jr.job = j.job
>      ORDER BY jr.this_date;

The following script shows failing or broken jobs (i.e., jobs that may need attention):

>     /* Filename on companion disk: job2.sql */*
>     col job_owner format a15
>     col job_definition format a30 word_wrap

>     SELECT  job
>            ,log_user     job_owner
>            ,failures
>            ,broken
>            ,what         job_definition
>       FROM
>             dba_jobs
>      WHERE
>             broken = 'Y' OR NVL(failures,0) > 0 ;

The next script shows jobs queued up to be executed in order of next execution date. Jobs with negative values in the mins_to_exec column indicate that the job queue is not keeping up with its workload and may need extra job queue processes initiated. The script excludes currently executing jobs because next_date will not be updated until the current execution completes.

>     /* Filename on companion disk: job2.sql */*
>     col job_definition format a30 word_wrap
>     col username format a15

>     ALTER SESSION SET NLS_DATE_FORMAT='YYYY:MM:DD:HH24:MI:SS';

>     SELECT  job
>            ,username
>            ,next_date
>            ,ROUND((next_date - SYSDATE)*24*60)  mins_to_exec
>            ,what                         job_definition
>       FROM
>             dba_jobs
>      WHERE
>           broken != 'Y'
>       AND job NOT IN
>              (SELECT job
>                 FROM dba_jobs_running)
>      ORDER BY next_date ASC;

Here is sample output from the preceding script on a system that has a very busy job queue. Job number 10 will be run next but is already 21 minutes late for execution.

>         JOB NEXT_DATE           MINS_TO_EXEC JOB_DEFINITION
>     ----- ------------------- ------------ ------------------------------
>        10 1997:11:25:17:04:10          -21 load3.loadx.loop_and_execute(5
>                                            ,30,'begin
>                                            loadx.table_scanner(5,5);end;'
>                                            );

>         5 1997:11:25:17:25:21            0 load3.loadx.loop_and_execute(1
>                                            0,90,'begin
>                                            loadx.cpu_hog(20,20,20);end;')
>                                            ;

>        12 1997:11:25:17:29:08            4 load2.loadx.loop_and_execute(2
>                                            0,60,'begin
>                                            loadx.grow_table(''LOAD2'',''T
>                                            ABLE2'',500,500);end;');

### 13.3.3 DBMS_IJOB: Managing Other Users' Jobs

One of the most frustrating aspects of the DBMS_JOB package for DBAs is that its procedures can be executed only against jobs owned by the current user. Even the SYS user cannot remove or set the broken flag for other user's jobs. Thus, job queue environments with multiple job owners can become problematic to administer using the DBMS_JOB package. On the other hand, requiring all jobs to be submitted under a single schema can introduce significant administrative overhead and complexity.

Fortunately, there is a way out of this dilemma. While it is not widely documented (until now), there is a hidden package interface into the job queue, which allows administrators to manipulate jobs that are not their own. This package is called DBMS_IJOB and it is created entirely in the prvtjob.plb script. DBMS_IJOB allows properly authorized users to manipulate any job in the job queue.

The following procedure uses DBMS_IJOB.BROKEN to set or unset the broken flag for all jobs in the job queue:

>     /* Filename on companion disk: job3.sql */*
>     PROCEDURE break_all_jobs (set_broken_IN IN BOOLEAN)
>     IS
>        /*
>        || Sets the broken flag to TRUE or FALSE for all
>        || jobs in the job queue
>        ||
>        || Requirements:
>        ||
>        || SELECT on DBA_JOBS
>        || EXECUTE on DBMS_IJOB
>        */
>     BEGIN
>        FOR job_rec IN
>                 (SELECT job
>                    FROM dba_jobs)
>        LOOP
>           SYS.DBMS_IJOB.BROKEN(job_rec.job, set_broken_IN);
>        END LOOP;
>     END break_all_jobs;

Another useful administrative feature of DBMS_IJOB is the ability to remove other users' jobs from the queue. Again, such activities should typically be done by DBAs and only when necessary. Here is a handy procedure similar to the previous one that will remove all jobs by user, or all jobs if NULL is explicitly passed in for the owner_IN parameter. If no job owner is specified, the procedure removes all jobs owned by the caller.

>     /* Filename on companion disk: job3.sql */*
>     PROCEDURE remove_all_jobs
>        (owner_IN IN VARCHAR2 := USER )
>     IS
>        /*
>        || Removes all jobs from the job queue owned by
>        || a specific user, defaults to current user
>        ||
>        || Requirements:
>        ||
>        || SELECT on DBA_JOBS
>        || EXECUTE on DBMS_IJOB
>        */
>     BEGIN
>        FOR job_rec IN
>                 (SELECT job
>                    FROM dba_jobs
>                   WHERE priv_user = NVL(owner_IN,priv_user) )
>        LOOP
>           SYS.DBMS_IJOB.REMOVE(job_rec.job);
>        END LOOP;
>     END remove_all_jobs;

These two procedures may come in handy when manipulation of large numbers of jobs is necessary (e.g., when trying to quiesce an environment with many busy job queue processes).

13.2 Job Queue Architecture |  | 13.4 DBMS_JOB Examples

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.