Oracle Taming the Job Scheduling Beast Oracle Tips by Burleson Consulting

Overview and Examples of dbms_job Functions

Prior to Oracle 10g, the dbms_job package was the preferred method for scheduling Oracle jobs.  With this package, jobs are created using either the submit or isubmit procedures, whose call specifications are displayed below:

PROCEDURE isubmit (
  job       IN  BINARY_INTEGER,
  what      IN  VARCHAR2,
  next_date IN  DATE,
  interval  IN  VARCHAR2 DEFAULT 'null',
  no_parse  IN  BOOLEAN DEFAULT FALSE)

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,
  instance  IN  BINARY_INTEGER DEFAULT 0,
  force     IN  BOOLEAN DEFAULT FALSE)

The parameters associated with these procedures and their usage are as follows:

* job - A number that uniquely identifies the job.

* what - A string that specifies the actual work that should be executed by the job.

* next_date - A date that specifies the next time the job will run.

* interval - A string that is evaluated when the job is executed to determine the next time the job should run.  The value NULL means the job should not run again.

* no_parse - A boolean that indicates if the validity of the job specified in the what parameter should be checked.

* instance - The instance number that is allowed to run this job, allowing the job queue affinity to be set.  By default, any instance can run the job.

* force - If TRUE, this parameter prevents errors when the job is defined against an instance that is not running.

The isubmit procedure allows the user to create a job and specify their own job number for it; whereas, the submit procedure allocates a job number for the user.  The following examples show how to use these procedures to schedule the my_job_proc procedure to run immediately, then once every hour after that.

BEGIN
  DBMS_JOB.isubmit (
    job       => 99,
    what      => 'my_job_proc(''DBMS_JOB.ISUBMIT Example.'');',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1/24 /* 1 Hour */');   

  COMMIT;
END;
/

VARIABLE l_job NUMBER;

BEGIN
  DBMS_JOB.submit (
    job       => :l_job,
    what      => 'my_job_proc(''DBMS_JOB.SUBMIT Example.'');',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1/24 /* 1 Hour */');
 
 COMMIT;
END;
/

PRINT l_job

The contents of the alert log show that both jobs executed correctly.

Tue Jun 22 10:09:02 2004
MY_JOB_PROC Start: DBMS_JOB.SUBMIT Example.
Tue Jun 22 10:11:25 2004
MY_JOB_PROC Start: DBMS_JOB.ISUBMIT Example.
Tue Jun 22 10:13:08 2004
MY_JOB_PROC End: DBMS_JOB.SUBMIT Example.
Tue Jun 22 10:13:09 2004
MY_JOB_PROC End: DBMS_JOB.ISUBMIT Example.

Information about the jobs can be shown using the dba_jobs view. The following script displays basic information about scheduled jobs for a specified user or all users.

* jobs.sql

-- *****************************************************************
-- Parameters:
--    1) Specific USERNAME or ALL which doesn't limit output.
-- *****************************************************************

set verify off
set linesize 120
set feedback off
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set feedback on

COLUMN job FORMAT 999
COLUMN what FORMAT A55
COLUMN interval FORMAT A30

select
   job,
   what,
   next_date,
   interval
from
   user_jobs
where
   schema_user = decode(upper('&1'), 'ALL', schema_user, upper('&1'))
order by
   next_date
;

The output generated by the jobs.sql script is displayed below.

SQL> @jobs job_user

JOB WHAT                                                    NEXT_DATE            INTERVAL
---- ------------------------------------------------------- -------------------- ----  99 my_job_proc('DBMS_JOB.ISUBMIT Example.');               22-JUN-2004 10:11:20 SYSDATE + 1/24 /* 1 Hour */

3 my_job_proc('DBMS_JOB.SUBMIT Example.');                 22-JUN-2004 10:11:20 SYSDATE + 1/24 /* 1 Hour */

2 rows selected.

Job information is also available from the Oracle Enterprise Manager (OEM) (Network --> Databases --> Your-Instance --> Distributed --> Advanced Replication --> Administration --> DBMS Job Tab).  Figure 2.1 shows the typical information displayed on this screen.

Figure 2.1 ? OEM: DBMS Jobs

Once a job is scheduled, a change to some of its attributes may be desired.  This can be achieved using the what, next_date, instance, interval and change procedures whose call specifications are displayed below.

PROCEDURE what (
  job       IN  BINARY_INTEGER,
  what      IN  VARCHAR2)

PROCEDURE next_date (
  job       IN  BINARY_INTEGER,
  next_date IN  DATE)

PROCEDURE instance (
  job       IN  BINARY_INTEGER,
  instance  IN  BINARY_INTEGER,
  force     IN  BOOLEAN DEFAULT FALSE)

PROCEDURE interval (
  job       IN  BINARY_INTEGER,
  interval  IN  VARCHAR2)

PROCEDURE 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)

The what, next_date, instance and interval procedures allows the individual attributes of the same name to be altered, while the change procedure allows all of them to be altered in one go, effectively replacing the existing job.  The examples below show how the procedures can be used:

BEGIN
  DBMS_JOB.what (
    job  => 99,
    what => 'my_job_proc(''DBMS_JOB.ISUBMIT Example (WHAT).'');');

  DBMS_JOB.next_date (
    job       => 99,
    next_date => SYSDATE + 1/12);

  DBMS_JOB.interval (
    job      => 99,
    interval => 'SYSDATE + 1/12 /* 2 Hours */');

  COMMIT;
END;
/

The output generated by the user_jobs.sql shows that the changes have been applied.

SQL> @jobs job_user

 JOB WHAT                                                    NEXT_DATE            INTERVAL
---- ------------------------------------------------------- -------------------- ----
   3 my_job_proc('DBMS_JOB.SUBMIT Example.');                22-JUN-2004 11:11:25 SYSDATE + 1/24 /* 1 Hour */

  99 my_job_proc('DBMS_JOB.ISUBMIT Example (WHAT).');        22-JUN-2004 12:24:13 SYSDATE + 1/12 /* 2 Hours */

2 rows selected.

The entire job definition can also be changed back using the change procedure.  If the what, next_date or interval parameters are NULL, the existing value is unchanged.

BEGIN
  DBMS_JOB.change (
    job       => 99,
    what      => 'my_job_proc(''DBMS_JOB.ISUBMIT Example.'');',
    next_date => TO_DATE('22-JUN-2004 10:11:20', 'DD-MON-YYYY HH24:MI:SS'),
    interval  => 'SYSDATE + 1/24 /* 1 Hour */');
  COMMIT;
END;
/

The output generated by the user_jobs.sql shows that the changes have been applied.

SQL> @jobs job_user

JOB  WHAT                                      NEXT_DATE            INTERVAL
--- ----------------------------------------- -------------------- ------------------- 99 my_job_proc('DBMS_JOB.ISUBMIT Example.'); 22-JUN-2004 11:36:23 SYSDATE + 1/24 /* 1 Hour */

  3 my_job_proc('DBMS_JOB.SUBMIT Example.');  22-JUN-2004 11:11:17 SYSDATE + 1/24 /* 1 Hour */

2 rows selected.

The broken procedure allows the broken flag associated with the job to be altered, as shown below.

PROCEDURE broken (
  job       IN  BINARY_INTEGER,
  broken    IN  BOOLEAN,
  next_date IN  DATE DEFAULT SYSDATE)

Jobs that are set as broken are not run, so this is a convenient way to temporarily stop them.  The following example shows how the broken procedure is used:

BEGIN
  DBMS_JOB.broken (
    job    => 99,
    broken => TRUE);

  DBMS_JOB.broken (
    job    => 99,
    broken => FALSE);   

  COMMIT;
END;
/

The job details, including the schedule, job definition and the broken flag, can be edited within the OEM by double clicking on the job of interest. Figure 2.2 shows the edit job dialog screen.

Figure 2.2 ? OEM: Edit job

The run procedure allows a specified job to run immediately with the next_date recalculated from that point.  The force parameter indicates that the job queue affinity can be ignored allowing any instance to run the job.

PROCEDURE run (
  job       IN  BINARY_INTEGER,
  force     IN  BOOLEAN DEFAULT FALSE)

The following example shows how to run a specific job:

BEGIN
  DBMS_JOB.run (
    job => 99);

  COMMIT;
END;
/

The user_export procedures allow the creation text for a specific job to be recreated.  Their call specifications are listed below.

PROCEDURE user_export (
  job    IN     BINARY_INTEGER,
  mycall IN OUT VARCHAR2)

PROCEDURE user_export (
  job    IN     BINARY_INTEGER,
  mycall IN OUT VARCHAR2,
  myinst IN OUT VARCHAR2)

The myinst parameter allows the job queue affinity to be set in the resulting statement.

To recreate the job creation script for job 99, do the following:

VARIABLE l_my_call  VARCHAR2(1000);

BEGIN
  DBMS_JOB.user_export (
    job    => 99,
    mycall => :l_my_call);
END;
/

PRINT l_my_call                           

L_MY_CALL
--------------------------------------------------------------------------------------
dbms_job.isubmit (job=>99,what=>'my_job_proc(''DBMS_JOB.ISUBMIT Example.'');',next_date=>to_date('2004-06-22:11:36:23','Y

YYY-MM-DD:HH24:MI:SS'),interval=>'SYSDATE + 1/24 /* 1 Hour */',no_parse=>TRUE);

When a job is no longer needed, it can be permanently removed from the queue using the remove procedure.

PROCEDURE remove (
  job  IN  BINARY_INTEGER)

The jobs created thus far can be removed using the following code.  Removing a job that is currently running does not stop it.

BEGIN
  DBMS_JOB.remove (
    job => 99);   

  DBMS_JOB.remove (
    job => 48);   

  COMMIT;
END;
/

Now that the basic functions of dbms_job have been presented, the data dictionary views that allow authorized users to view information about scheduled jobs will be examined.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.