25-1.

You need DBMS_JOB to schedule and execute PL/SQL procedures at predefined times or at regular intervals, such as routine maintenance procedures or developer batch jobs.

25-2.

The three INIT.ORA parameters that affect DBMS_JOB are:

25-3.

Since only one concurrent process is allowed, the second job must wait until the first job is complete.

25-4.

No, it’s not possible. Every started job establishes a lock in the V$LOCK table to prevent other processes from reexecuting the job.

25-5.

DBMS_JOB has two procedures to submit a job into the queue: SUBMIT and ISUBMIT. Both procedures share the following parameters:

25-6.

SUBMIT assigns a unique ID for the job automatically from the sequence SYS.JOBSEQ, and returns this number in the OUT parameter job. ISUBMIT allows you to supply your own job ID with the IN parameter job. When using ISUBMIT, you’re responsible for guaranteeing a unique ID. Otherwise, you receive the following error:

ORA-00001: unique constraint (SYS.I_JOB_JOB) violated

25-7.

Even though Oracle documentation states that it is not necessary, you should always issue a COMMIT after each call to SUBMIT or ISUBMIT:

BEGIN
   DBMS_JOB.ISUBMIT ( 33, 'calc_totals;', SYSDATE, 'SYSDATE + 1'),
   COMMIT;
END;
/

25-8.

The job never executes because the next_date parameter is set to NULL, which causes DBMS_JOB to set the next execution date to January 1, 4000 (effectively preventing the execution of the job). It allows you to submit a job, but does not yet submit it for execution.

25-9.

You must remember to end your stored procedure call or anonymous block with a semicolon. Oracle doesn’t do it for you.

DBMS_JOB.ISUBMIT (job => 33,
                  what => 'foo;',
                  next_date => NULL,
                  interval => 'SYSDATE + 1'),

25-10.

No; in fact, probably the only thing you can be sure of is it won’t be 1:00 P.M.! To see why, suppose that interval is set to 60 (the default). On average, you’ll have a 30-second delay every time before starting the job, resulting in a 3-hour (30 * 365 / 3600) delay per year! This cumulative shifting (i.e., later and later), or drift, is inherent in interval expressions such as ‘SYSDATE + 1’.

To set an exact time, use the TRUNC function and date arithmetic in the INTERVAL parameter to set up the next execution time precisely:

DBMS_JOB.ISUBMIT (
      job => 33,
      what => 'DUMP_USERS;',
      next_date => SYSDATE,
      interval => 'TRUNC(SYSDATE) + 13/24'
   );

The TRUNC function truncates the current date to noon, and + 13/24 means “add 13 hours from midnight” (i.e., 1:00 P.M.). Such declarations ensure that the next execution time is close to 1:00 P.M., regardless of the previous execution time. If you want to operate in minutes, not hours, use the formula ‘minutes / (24 * 60)’ to qualify the number of minutes you want to add.

25-11.

There are two ways to temporarily stop a job (without, of course, deleting it from the queue). The first option is to use the DBMS_JOB.BROKEN procedure to set the broken flag to TRUE for the job, since broken jobs are never run:

DBMS_JOB.BROKEN (job => 33, broken => TRUE);

To return the job to its normal state, simply set the broken flag back to FALSE.

The second method is to change the next_date parameter to some date far in the future using DBMS_JOB.NEXT_DATE:

DBMS_JOB.NEXT_DATE (
      job => 33,
      next_date => TO_DATE ('01.01.3000', 'DD.MM.YYYY')
   );

Better yet, you can set next_date to NULL, which, as we’ve already seen, will automatically set the next execution date to January 1, 4000:

DBMS_JOB.NEXT_DATE (job => 33, next_date => NULL);

25-12.

Yes, you can use the DBMS_JOB.RUN procedure to run the task. This sets the broken flag to FALSE if execution was successful. So after improving and compiling the foo procedure, all you need to do is to run Job 33:

DBMS_JOB.RUN (job => 33);
COMMIT;

This way you can both return the job to the job queue and test to make sure it runs.

25-13.

Submit the job with a NULL value in the interval parameter. For example, if you have a table you want to clear out three days hence, you can use the following call:

DBMS_JOB.ISUBMIT (
      job => 34,
      what => 'delete from CONFIDENTIAL_DOCS;',
      next_date => TRUNC (SYSDATE) + 3,
      interval => NULL
   );

25-14.

You can use the DBMS_JOB.RUN procedure to force an immediate execution. This should be done with caution, however, as there are a number of possible side effects. For example:

25-15.

The ALL_JOBS and DBA_JOBS data dictionary views provide you with information about all jobs in the queue. Although these views have a huge number of columns, the most interesting columns are JOB, LAST_DATE (last successful execution), NEXT_DATE (next planned execution), LOG_USER (job owner), BROKEN, INTERVAL, FAILURES (number of unsuccessful executions), and WHAT.

The DBA_JOBS_RUNNING view provides information about currently running jobs.

25-16.

The following script shows the status of all the jobs in the queue:

ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI DD MON';
SELECT job,
       SUBSTR (
          DECODE (
             broken, 'Y',
             'BROKEN', DECODE (
                          failures,
                          0, 'OK',
                          NULL, 'NEW ',
                          TO_CHAR (failures) || ' FAILURES'
                       )
          ),
          1,
          11
       )
             status,
       SUBSTR (log_user, 1, 15) owner,
       last_date,
       next_date,
       SUBSTR (what, 1, 20) what
  FROM dba_jobs
 ORDER BY next_date;

This script shows NEW in the STATUS column for just-submitted jobs, OK for successful jobs, BROKEN for already broken, and number of failures otherwise. Here is sample output:

JOB       STATUS      OWNER           LAST_DATE    NEXT_DATE    WHAT
--------- ----------- --------------- ------------ ------------ -----
       17 OK          PLV             19:19 07 MAR 19:20 07 MAR test
       33 3 FAILURES  PLV                          19:26 07 MAR AAA;
        2 OK          PLV             10:39 07 MAR 01:28 08 MAR begin
       36 OK          PLV             15:30 07 MAR 15:30 08 MAR NULL;
       34 NEW         PLV                          00:00 10 MAR delete
    10000 BROKEN      PLV             00:19 05 MAR 00:00 01 JAN update

25-17.

This script shows the ID of the session where job is running, the username for this session, the job number, and the time the job was started:

ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI DD MON';
SELECT r.sid,
       s.username,
       j.job,
       r.this_date,
       SUBSTR (j.what, 1, 20) what
  FROM dba_jobs_running r,
       dba_jobs j,
       v$session s
 WHERE j.job = r.job
   AND r.sid = s.sid;

25-18.

Although doing so would be wonderful, you should not use user-defined functions (in this case, next_business_day) in the interval parameter. Although you can submit such jobs, they don’t execute at the correct times. Consequently, you should use PL/SQL date functions only.

25-19.

You can use the WHAT, NEXT_DATE, and INTERVAL procedures from the DBMS_JOB package. Each procedure changes the corresponding parameter. Another procedure, CHANGE, can change any or all of the parameters. For example, to change the execution interval for job #33, issue the call:

DBMS_JOB.INTERVAL (job => 33, interval => 'TRUNC(SYSDATE) + 7'),

Note that you can’t change the job number.

25-20.

It’s really easy; simply create a PL/SQL block with two commands instead of one:

DBMS_JOB.ISUBMIT (
      job => 36,
      what => ' BEGIN LFF; PRR; END;',
      next_date => SYSDATE,
      interval => 'SYSDATE + 1'
   );

25-21.

You can make a job jump to the head of the queue by setting its next_date parameter to some date in the past:

DBMS_JOB.ISUBMIT (
      job => 58,
      what => 'URGENT_JOB;',
      next_date => TRUNC (SYSDATE) - 1,
      interval => NULL
   );

This works because Oracle selects jobs for execution based on the order of next_date; the new job is fetched first (assuming, of course, you have no jobs with a next_date earlier that what you set your job to).

25-22.

The first way to remove the job is to use DBMS_JOB.REMOVE:

DBMS_JOB.REMOVE (job => 58);

The second way is to change the job’s interval parameter to NULL:

DBMS_JOB.INTERVAL (job => 58, interval => NULL);

Note that in either case, if the job is already running when you issue the command, it is allowed to finish and then is removed from the job queue. Consequently, you can’t use either method to “kill” an abnormal or long-running job.

25-23.

DBMS_JOB has a special procedure for this purpose: DBMS_JOB.USER_EXPORT. Calling this procedure returns, via a VARCHAR2 OUT parameter, a string consisting of a call to DBMS_JOB.ISUBMIT. You can then execute this string in the other database to recreate the job. Remember that the other database could already have a job with the same number, so you must check this and adjust the job number if necessary. The job number can be adjusted upwards by issuing the following SELECT until it is at an appropriate value:

SELECT SYS.JOBSEQ.NEXTVAL FROM dual;

25-24.

When the job execution fails, Oracle attempts to start it again one minute later. If it fails again, Oracle repeatedly doubles the amount of time it waits before the next execution attempt (i.e., 2, 4, 8, 16, etc., minutes), until it either exceeds the normal execution interval or fails 16 times. In this case, the job is flagged as broken.

25-25.

The actual sequence is as follows:

25-26.

Since jobs running as background processes have no associated terminal, it’s impossible to put an error message to the terminal. Consequently, to find an error that occurred during execution, you must check the log files to find the error number(s) and message(s).

To do this, check either the RDBMSxx/TRACE directory in the Oracle home catalog or the path defined in the BACKGROUND_DUMP_DEST parameter of the INIT.ORA file. While the name of the SNP’s log file depends on the platform, it typically consists of a concatenation of the instance’s name, the literal SNP, and the SNP process number. For example:

orclSNP0.TRC
orclSNP1.TRC

Open the file and check the last message to find the error.

Alternatively, you can force the job to run using the DBMS_JOB.RUN procedure. In this case you’ll see the error messages, if any, on the screen.

25-27.

There are two ways to check if a job is currently running. First, you can look in the DBA_JOBS_RUNNING view for particular job number:

SELECT * FROM dba_jobs_running WHERE job = 36;

Second, you can query the V$LOCK for a running job because Oracle establishes a special lock (lock type = ‘JQ’) for running jobs. The job number is defined in the ID2 column:

SELECT id2 "ID2->JOB#" FROM v$lock WHERE TYPE = 'JQ';

25-28.

The following script shows the execution time (in minutes) for all current jobs:

SELECT r.job,
       ROUND ((SYSDATE - r.this_date) * 60 * 24) execution,
       SUBSTR (j.what, 1, 30) what
  FROM dba_jobs_running r,
       dba_jobs j
 WHERE r.job = j.job;

25-29.

You can use any date functions. The most useful are:

25-30.

The interval is:

'SYSDATE + 1/24';

25-31.

The interval is:

'SYSDATE + 1/(24*60)'

25-32.

The interval is:

'SYSDATE + 1/(24*60*60)'

25-33.

The interval is:

'TRUNC(SYSDATE+1) + 11/24'

25-34.

The interval is:

'NEXT_DAY(TRUNC(SYSDATE), "FRIDAY") + 21/24'

25-35.

The interval is:

'TRUNC(  LEAST( NEXT_DAY(SYSDATE,"SATURDAY"), ' ||
'               NEXT_DAY(SYSDATE,"SUNDAY"))) + 6/24 '

25-36.

The interval is:

'LAST_DAY(SYSDATE + 1) + 23/24'

25-37.

You can retrieve the value of a job’s execution definition by including a special parameter in the job definition. The parameter must have one of the following names: job, next_date, or broken. Whenever Oracle sees one of these special names, it substitutes the corresponding value for the execution definition:

DBMS_JOB.ISUBMIT (
      job => 36,
      what => ' MY_PROC(job, next_date, broken_flag);',
      next_date => SYSDATE,
      interval => 'SYSDATE + 1'
   );

Since next_date and broken are treated as IN OUT parameters, you can allow a job to alter its schedule. For example, the job could change its next execution date regardless of interval, set itself into the broken state, or even remove itself from the job queue.

25-38.

First, create a table to store the log:

CREATE TABLE user_log (
   tstamp    DATE,
   username  VARCHAR2(30),
   osuser    VARCHAR2(15),
   machine   VARCHAR2(64),
   program   VARCHAR2(64),
   module    VARCHAR2(48));

Now, write the procedure itself. Since there’s a fairly complex schedule, you need to make dump_users modify its job-definition parameters:

CREATE OR REPLACE PROCEDURE dump_users (next_date IN OUT DATE)
IS
   d NUMBER   --detect weekday
           := TO_NUMBER (TO_CHAR (next_date, 'D')) - 1;
   h NUMBER   --detect hour
           := TO_NUMBER (TO_CHAR (next_date, 'HH24'));
BEGIN
   /*
   || Set execution schedule:
   || If day is a weekend (i.e. Sunday or Saturday) or
   || hour before 8:00 AM or after 6:00 PM, then execute hourly.
   || Otherwise, execute at 7:00 PM
   */
   IF (d IN (0, 6)) OR (h < 8 OR h > 18)
   THEN
      -- then execute every hour
      next_date := TRUNC (next_date, 'HH24') + 1 / 24;
   ELSE
      -- execute at 7 PM
      next_date := TRUNC (next_date) + 19 / 24;
   END IF;

   /*
   || Save V$SESSION information into the log table
   */
   INSERT INTO user_log
      SELECT SYSDATE,
             username,
             osuser,
             machine,
             program,
             module
        FROM v$session
       WHERE TYPE != 'BACKGROUND'
         AND username IS NOT NULL;

   COMMIT;
END;

Now you need to submit this job. There are two things to note. First, you need to use the special next_date parameter as part of the job definition. Second, you can use any correct nonnull interval value since you are computing the next execution date yourself:

DBMS_JOB.ISUBMIT (
      job => 88,
      what => 'DUMP_USERS(next_date);',
      next_date => SYSDATE,
      interval => 'SYSDATE + 1'
   );

25-39.

As we’ve seen, the REMOVE procedure doesn’t stop a running job; you need, instead, to use the ALTER SYSTEM KILL SESSION command, which requires two parameters: the SID of the job you want to kill and the job’s serial number. You can find the SID in the DBA_JOBS_RUNNING view, and the job’s serial number in the V$SESSION view:

SELECT r.job,
       r.sid,
       s.serial#
  FROM dba_jobs_running r,
       v$session s
 WHERE r.sid = s.sid
   AND r.job = 47;

      JOB       SID   SERIAL#
--------- --------- ---------
       47         7         3

The next step is to mark the job broken to prevent future execution:

DBMS_JOB.BROKEN( 47, TRUE);
COMMIT;

Finally, issue the KILL command:

ALTER SYSTEM KILL SESSION '7,3';

25-40.

You’ll find a suggested procedure in the runjob.sp file on the book’s web page.

25-41.

We have:

next_date IN VARCHAR2,

in ISUBMIT and:

next_date IN DATE DEFAULT sysdate,

in SUBMIT. Since this makes no sense, it’s probably just somebody’s mistake during the creation of the DBMS_JOB package. What is really strange, however, is that this mistake migrates from version to version without changes, which causes two problems. The first is that since the next_date parameter doesn’t have a default value, you can’t omit the parameter in the ISUBMIT procedure. The second is that when you use an NLS_DATE_FORMAT different from the current default for the database instance, the ISUBMIT procedure truncates next_date to the beginning of the day. Here’s an example of this:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';
Session altered.
SQL> EXEC DBMS_JOB.ISUBMIT(45,'NULL;',SYSDATE + 25/24,'SYSDATE + 1'),
PL/SQL procedure successfully completed.
SQL> SELECT TO_CHAR(NEXT_DATE,'HH24:MI DD/MM') "WRONG!"
   2   FROM DBA_JOBS where JOB=45;
WRONG!
---------------------------------------------------------------------------
00:00 08/03

The only way to submit your desired next_date in this case is to do it in two steps:

DBMS_JOB.ISUBMIT (
      job => 46,
      what => 'NULL;',
      next_date => NULL,   -- WILL BE SET TO 01.01.4000
      interval => 'SYSDATE + 1'
   );
   DBMS_JOB.NEXT_DATE (job => 46,   -- SET DESIRED DATE
                       next_date => SYSDATE + 25 / 24);
   COMMIT;