Oracle Job原理--Managing job queues

蔚落 2022-08-24 05:20 232阅读 0赞

# 10 Managing Job Queues #

This chapter describes how to use job queues to schedule the periodic execution of user jobs, and contains the following topics:

*  [Enabling Processes Used for Executing Jobs][]
 *  [Managing Job Queues][]
 *  [Viewing Job Queue Information][]

## Enabling Processes Used for Executing Jobs ##

You can schedule routines (jobs) to be run periodically using the job queue. To schedule a job you submit it to the job queue, using the Oracle supplied `DBMS_JOB` package, and specify the frequency at which the job is to be run. Additional functionality enables you to alter, disable, or delete a job that you previously submitted.

Job queue (J*nnn)* processes execute jobs in the job queue. For each instance, these job queue processes are dynamically spawned by a coordinator job queue (CJQ0) background process. The coordinator periodically selects jobs that are ready to run from the jobs shown in the `DBA_JOBS` view. It orders them by time, and then spawns J*nnn* processes to run the selected jobs. Each J*nnn* process executes one of the selected jobs.

The `JOB_QUEUE_PROCESSES` initialization parameter controls whether a coordinator job queue process is started by an instance. If this parameter is set to 0, no coordinator job queue process is started at database startup, and consequently no job queue jobs are executed. The `JOB_QUEUE_PROCESSES` initialization parameter also specifies the maximum number of J*nnn* processes that can concurrently run on an instance. The maximum number of processes that can be specified is 1000.

The following initialization parameter setting causes the coordinator job queue process to start at database startup, and allows the spawning of a maximum of 60 concurrent J*nnn* processes.

JOB_QUEUE_PROCESSES = 60

In any given period that the coordinator job queue process scans the jobs shown in the `DBA_JOBS` view, it spawns at most only the number of J*nnn* processes required to execute the jobs it has selected. While the above example allows for 60 concurrent J*nnn* processes, if only 20 jobs are selected for execution, then the coordinator spawns, or reuses, only the number of J*nnn* processes necessary to execute the 20 jobs (at least, 20). Any idle existing J*nnn* processes are considered available for reuse.

When a J*nnn* process finishes execution of a job, it polls for another job to execute. If there are no jobs selected for execution, it enters an idle state, but wakes up periodically to poll again. If, after a predetermined number of tries, it still finds no jobs to execute, it terminates.

The `JOB_QUEUE_PROCESSES` initialization parameter is dynamic and it can be modified by an `ALTER SYSTEM` statement. For example, the following statement sets the maximum number of concurrent J*nnn* processes allowed to 20.

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;

If the new value is lower than the previous setting and less than the number of currently executing J*nnn* processes, the excess processes are allowed to complete before they are terminated.

J*nnn* processes will not execute jobs if the instance is running in restricted mode.

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td><a rel="nofollow"></a><font><strong>See also:</strong></font> <a rel="nofollow"></a> <p><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/start.htm#6289" rel="nofollow"><u><font>"Restricting Access to an Open Database"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/start.htm#6289" rel="nofollow"></a> for information about enabling and disabling restricted mode</p></td> 
  </tr> 
 </tbody> 
</table>

## Managing Job Queues ##

This section describes the various aspects of managing job queues and contains the following topics:

*  [The DBMS\_JOB Package][The DBMS_JOB Package]
 *  [Submitting a Job to the Job Queue][]
 *  [How Jobs Execute][]
 *  [Removing a Job from the Job Queue][]
 *  [Altering a Job][]
 *  [Broken Jobs][]
 *  [Forcing a Job to Execute][]
 *  [Terminating a Job][]

### The DBMS\_JOB Package ###

To schedule and manage jobs in the job queue, use the procedures in the `DBMS_JOB` package. There are no database privileges associated with using job queues. Any user who can execute the job queue procedures can use the job queue.

The following are procedures of the `DBMS_JOB` package. They are described in this section as noted.

<table title=""> 
 <thead> 
  <tr> 
   <th align="left"><a rel="nofollow"></a><font><strong>Procedure</strong></font></th> 
   <th align="left"><a rel="nofollow"></a><font><strong>Description</strong></font></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>SUBMIT</code></p></td> 
   <td><a rel="nofollow"></a> <p>Submits a job to the job queue. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#7997" rel="nofollow"><u><font>"Submitting a Job to the Job Queue"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#7997" rel="nofollow"></a>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>REMOVE</code></p></td> 
   <td><a rel="nofollow"></a> <p>Removes a specified job from the job queue. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1057" rel="nofollow"><u><font>"Removing a Job from the Job Queue"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1057" rel="nofollow"></a>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>CHANGE</code></p></td> 
   <td><a rel="nofollow"></a> <p>Alters a specified job that has already been submitted to the job queue. You can alter the job description, the time at which the job will be run, or the interval between executions of the job. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1079" rel="nofollow"><u><font>"Altering a Job"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1079" rel="nofollow"></a>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>WHAT</code></p></td> 
   <td><a rel="nofollow"></a> <p>Alters the job description for a specified job. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1079" rel="nofollow"><u><font>"Altering a Job"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1079" rel="nofollow"></a>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>NEXT_DATE</code></p></td> 
   <td><a rel="nofollow"></a> <p>Alters the next execution time for a specified job. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1079" rel="nofollow"><u><font>"Altering a Job"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1079" rel="nofollow"></a>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>INTERVAL</code></p></td> 
   <td><a rel="nofollow"></a> <p>Alters the interval between executions for a specified job. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1079" rel="nofollow"><u><font>"Altering a Job"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1079" rel="nofollow"></a>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>BROKEN</code></p></td> 
   <td><a rel="nofollow"></a> <p>Sets or resets the job broken flag. If a job is marked as broken, Oracle does not attempt to execute it. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1129" rel="nofollow"><u><font>"Broken Jobs"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1129" rel="nofollow"></a>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>RUN</code></p></td> 
   <td><a rel="nofollow"></a> <p>Forces a specified job to run. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#2051" rel="nofollow"><u><font>"Forcing a Job to Execute"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#2051" rel="nofollow"></a>.</p></td> 
  </tr> 
 </tbody> 
</table>

### Submitting a Job to the Job Queue ###

To submit a new job to the job queue, use the `SUBMIT` procedure in the `DBMS_JOB` package. You specify the following parameters with the `SUBMIT` procedure:

<table title=""> 
 <thead> 
  <tr> 
   <th align="left"><a rel="nofollow"></a><font><strong>Parameter</strong></font></th> 
   <th align="left"><a rel="nofollow"></a><font><strong>Description</strong></font></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>JOB</code></p></td> 
   <td><a rel="nofollow"></a> <p>An output parameter. This is the identifier assigned to the job you are creating. You must use this job number whenever you want to alter or remove the job. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#935" rel="nofollow"><u><font>"Job Number"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#935" rel="nofollow"></a>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>WHAT</code></p></td> 
   <td><a rel="nofollow"></a> <p>This is the PL/SQL code you want to have executed. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#8434" rel="nofollow"><u><font>"Job Definition"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#8434" rel="nofollow"></a>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>NEXT_DATE</code></p></td> 
   <td><a rel="nofollow"></a> <p>This is the next date when the job will be run. The default value is <code>SYSDATE</code>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>INTERVAL</code></p></td> 
   <td><a rel="nofollow"></a> <p>This is the date function that calculates the next time to execute the job. The default value is <code>NULL</code>. <code>INTERVAL</code> must evaluate to a future point in time or <code>NULL</code>. See <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#966" rel="nofollow"><u><font>"Job Execution Interval"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#966" rel="nofollow"></a>.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>NO_PARSE</code></p></td> 
   <td><a rel="nofollow"></a> <p>This is a flag. If <code>NO_PARSE</code> is set to <code>FALSE</code> (the default), Oracle parses the procedure associated with the job. If <code>NO_PARSE</code> is set to <code>TRUE</code>, Oracle parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set <code>NO_PARSE</code> to <code>TRUE</code>.</p></td> 
  </tr> 
 </tbody> 
</table>

For example, consider the following statements that submit a new job to the job queue, then prints the job number. The job calls the procedure `DBMS_DDL.ANALYZE_OBJECT` to generate statistics for the table `hr.employees`. The statistics are based on a sample of half the rows of the `employees` table. The job is run every 24 hours.

VARIABLE jobno NUMBER 
    BEGIN
       DBMS_JOB.SUBMIT(:jobno, 
          'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',
          ''HR'', ''EMPLOYEES'', 
          ''ESTIMATE'', NULL, 50);', 
          SYSDATE, 'SYSDATE + 1');
       COMMIT;
    END;
    /
    PRINT jobno
    
    JOBNO
    ----------
    14144

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td> 
    <hr> <a rel="nofollow"></a><font><strong>Note:</strong></font> <a rel="nofollow"></a> <p>For the submitted job to run, you must issue a <code>COMMIT</code> statement immediately after the <code>DBMS_JOB.SUBMIT</code> statement.</p> 
    <hr> </td> 
  </tr> 
 </tbody> 
</table>

#### Job Environment ####

When you submit a job to the job queue or alter a job's definition, Oracle records the following environment characteristics:

*  The current user
 *  The user submitting or altering a job
 *  The current schema (may be different from current user or submitting user if `ALTER SESSION SET CURRENT_SCHEMA` statement has been issued)

Oracle also records the following NLS parameters:

*  `NLS_LANGUAGE`
 *  `NLS_TERRITORY`
 *  `NLS_CURRENCY`
 *  `NLS_ISO_CURRENCY`
 *  `NLS_NUMERIC_CHARACTERS`
 *  `NLS_DATE_FORMAT`
 *  `NLS_DATE_LANGUAGE`
 *  `NLS_SORT`

Oracle restores all of these environment characteristics every time a job is executed. `NLS_LANGUAGE` and `NLS_TERRITORY` parameters determine the defaults for unspecified NLS parameters.

You can change a job's environment by using the `DBMS_SQL` package and the `ALTER SESSION` statement.

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td><a rel="nofollow"></a><font><strong>See Also:</strong></font> <a rel="nofollow"></a> 
    <ul> 
     <li><a rel="nofollow"></a><em><a href="http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_sql.htm#ARPLS058" rel="nofollow"><u><font>Oracle9i Supplied PL/SQL Packages and Types Reference</font></u></a></em> for more information about the <code>DBMS_SQL</code> package </li> 
     <li><a rel="nofollow"></a><em><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_22a.htm#SQLRF00901" rel="nofollow"><u><font>Oracle9i SQL Reference</font></u></a></em> for information about use of the <code>ALTER SESSION</code> statement to alter a job's environment </li> 
    </ul></td> 
  </tr> 
 </tbody> 
</table>

#### Jobs and Import/Export ####

Jobs can be exported and imported. Thus, if you define a job in one database, you can transfer it to another database. When exporting and importing jobs, the job's number, environment, and definition remain unchanged.

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td> 
    <hr> <a rel="nofollow"></a><font><strong>Note:</strong></font> <a rel="nofollow"></a> <p>If the job number of a job you want to import matches the number of a job already existing in the database, you will not be allowed to import that job. Submit the job as a new job in the database.</p> 
    <hr> </td> 
  </tr> 
 </tbody> 
</table>

#### Job Owner ####

When you submit a job to the job queue, Oracle identifies you as the owner of the job. Only a job's owner can alter the job, force the job to run, or remove the job from the queue.

#### Job Number ####

A queued job is identified by its job number. When you submit a job, its job number is automatically generated from the `JOBSEQ` sequence owned by user `SYS`. Once a job is assigned a job number, that number does not change. Even if the job is exported and imported, its job number remains the same.

#### Job Definition ####

The job definition is the PL/SQL code specified in the `WHAT` parameter of the `SUBMIT` procedure. Normally, the job definition is a single call to a procedure. The procedure call can have any number of parameters.

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td> 
    <hr> <a rel="nofollow"></a><font><strong>Note:</strong></font> <a rel="nofollow"></a> <p>In the job definition, use two single quotation marks around strings. Always include a semicolon at the end of the job definition.</p> 
    <hr> </td> 
  </tr> 
 </tbody> 
</table>

The following are examples of valid job definitions:

*  `'myproc(''10-JAN-99'', next_date, broken);'`
 *  `'scott.emppackage.give_raise(''JFEE'', 3000.00);'`
 *  `'dbms_job.remove(job);'`
    
    <table title="This is a layout table to format a note"> 
     <tbody> 
      <tr> 
       <td> 
        <hr> <a rel="nofollow"></a><font><strong>Note:</strong></font> <a rel="nofollow"></a> <p>Running a job from a job is not supported. You will receive an error message if you attempt to do so. For example, the following statements produce the "<code>ORA-32317 cannot run a job from another job</code>" error message in the alert file:</p><pre><a rel="nofollow"></a>DECLARE 
    <a rel="nofollow"></a>  jobno number;
    <a rel="nofollow"></a>BEGIN
    <a rel="nofollow"></a>  DBMS_JOB.SUBMIT(jobno, 'DBMS_JOB.RUN(23587);');
    <a rel="nofollow"></a>  DBMS_JOB.RUN(jobno);
    <a rel="nofollow"></a>END;
    <a rel="nofollow"></a>/
    </pre> 
        <hr> </td> 
      </tr> 
     </tbody> 
    </table>

#### Job Execution Interval ####

If a job should be executed periodically at a set interval, use a date expression similar to `'SYSDATE + 7'` in the `INTERVAL` parameter. Below are shown some common date expressions used for job execution intervals.

<table title=""> 
 <thead> 
  <tr> 
   <th align="left"><a rel="nofollow"></a><font><strong>Date Expression</strong></font></th> 
   <th align="left"><a rel="nofollow"></a><font><strong>Evaluation</strong></font></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><pre><a rel="nofollow"></a>'SYSDATE + 7'
</pre></td> 
   <td><a rel="nofollow"></a> <p>Exactly seven days from the last execution</p></td> 
  </tr> 
  <tr> 
   <td><pre><a rel="nofollow"></a>'SYSDATE + 1/48'
</pre></td> 
   <td><a rel="nofollow"></a> <p>Every half hour</p></td> 
  </tr> 
  <tr> 
   <td><pre><a rel="nofollow"></a>'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24'
</pre></td> 
   <td><a rel="nofollow"></a> <p>Every Monday at 3PM</p></td> 
  </tr> 
  <tr> 
   <td><pre><a rel="nofollow"></a>'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3), ''THURSDAY'')'
</pre></td> 
   <td><a rel="nofollow"></a> <p>First Thursday of each quarter</p></td> 
  </tr> 
 </tbody> 
</table>

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td> 
    <hr> <a rel="nofollow"></a><font><strong>Note:</strong></font> <a rel="nofollow"></a> <p>When specifying <code>NEXT_DATE</code> or <code>INTERVAL</code>, remember that date literals and strings must be enclosed in single quotation marks. Also, the value of <code>INTERVAL</code> must be enclosed in single quotation marks.</p> 
    <hr> </td> 
  </tr> 
 </tbody> 
</table>

The `INTERVAL` date function is evaluated immediately before a job is executed. When the job completes successfully, the date calculated from `INTERVAL` becomes the new `NEXT_DATE`. For example, if you set the execution interval to `'SYSDATE + 7'` on Monday, but for some reason (such as a network failure) the job is not executed until Thursday, `'SYSDATE + 7'` then executes every Thursday, not Monday. If the `INTERVAL` date function evaluates to `NULL` and the job completes successfully, the job is deleted from the queue.

If you always want to automatically execute a job at a specific time, regardless of the last execution (for example, every Monday), the `INTERVAL` and `NEXT_DATE` parameters should specify a date expression similar to `'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'')'`.

#### Database Links and Jobs ####

If you submit a job that uses a database link, the link must include a username and password. Anonymous database links will not succeed.

### How Jobs Execute ###

J*nnn* processes execute jobs. To execute a job, the process creates a session to run the job. When a J*nnn* process runs a job, the job is run in the same environment in which it was submitted and with the *owner's* default privileges. The owner must be explicitly granted the necessary object privileges for all objects referenced within the job definition.

When you force a job to run using the procedure `DBMS_JOB.RUN`, the job is run by your user process and with *your* default privileges only. Privileges granted to you through roles are unavailable. You must be explicitly granted the necessary object privileges for all objects referenced within the job definition.

#### Job Queue Locks ####

Oracle uses job queue locks to ensure that a job is executed in only one session at a time. When a job is being run, its session acquires a job queue (JQ) lock for that job. You can use the locking views in the data dictionary to examine information about locks currently held by sessions.

The following query lists the session identifier, lock type, and lock identifiers for all sessions holding JQ locks:

SELECT SID, TYPE, ID1, ID2
       FROM V$LOCK
       WHERE TYPE = 'JQ';
    
          SID TY       ID1       ID2
    --------- -- --------- ---------
           12 JQ         0     14144
    1 row selected.

In the query above, the identifier for the session holding the lock is 12. The `ID1` column is always 0 for JQ locks. The `ID2` column is the job number of the job the session is running. This view can be joined with the `DBA_JOBS_RUNNING` view to obtain more information about the job.

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td><a rel="nofollow"></a><font><strong>See Also:</strong></font> <a rel="nofollow"></a> 
    <ul> 
     <li><a rel="nofollow"></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1227" rel="nofollow"><u><font>"Viewing Job Queue Information"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1227" rel="nofollow"></a> for more information about views </li> 
     <li><a rel="nofollow"></a><em><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/toc.htm" rel="nofollow"><u><font>Oracle9i Database Reference</font></u></a></em> for more information about the V$LOCK view </li> 
     <li><a rel="nofollow"></a><em><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#CNCPT021" rel="nofollow"><u><font>Oracle9i Database Concepts</font></u></a></em> for more information about locking </li> 
    </ul></td> 
  </tr> 
 </tbody> 
</table>

#### Job Execution Errors ####

When a job fails, information about the failure is recorded in a trace file and the alert log. Oracle writes message number `ORA-12012` and includes the job number of the failed job.

The following can prevent the successful execution of queued jobs:

*  A network or instance failure
 *  An exception when executing the job

If a job returns an error while Oracle is attempting to execute it, Oracle tries to execute it again. The first attempt is made after one minute, the second attempt after two minutes, the third after four minutes, and so on, with the interval doubling between each attempt. If the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it. However, between attempts, you have the opportunity to correct the problem that is preventing the job from running. This will not disturb the retry cycle, and Oracle will eventually attempt to run the job again.

### Removing a Job from the Job Queue ###

To remove a job from the job queue, use the `REMOVE` procedure in the `DBMS_JOB` package.

The following statements remove job number 14144 from the job queue:

BEGIN
    DBMS_JOB.REMOVE(14144);
    END;
    /

##### Restrictions: #####

*  You can remove currently executing jobs from the job queue. However, the job will not be interrupted, and the current execution will be completed.
 *  You can remove only jobs you own. If you try to remove a job that you do not own, you receive a message that states the job is not in the job queue.

### Altering a Job ###

To alter a job that has been submitted to the job queue, use the procedures `CHANGE`, `WHAT`, `NEXT_DATE`, or `INTERVAL` in the `DBMS_JOB` package.

##### Restriction: #####

*  You can alter only jobs that you own. If you try to alter a job that you do not own, you receive a message that states the job is not in the job queue.

#### CHANGE ####

You can alter any of the user-definable parameters associated with a job by calling the `DBMS_JOB.CHANGE` procedure.

In this example, job number 14144 is altered to execute every three days:

BEGIN
    DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3');
    END;
    /

If you specify `NULL` for `WHAT`, `NEXT_DATE`, or `INTERVAL` when you call the procedure `DBMS_JOB.CHANGE`, the current value remains unchanged.

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td> 
    <hr> <a rel="nofollow"></a><font><strong>Note:</strong></font> <a rel="nofollow"></a> <p>When you change a job's definition using the <code>WHAT</code> parameter in the procedure <code>DBMS_JOB.CHANGE</code>, Oracle records your current environment. This becomes the new environment for the job.</p> 
    <hr> </td> 
  </tr> 
 </tbody> 
</table>

#### WHAT ####

You can alter the definition of a job by calling the `DBMS_JOB.WHAT` procedure.

The following example changes the definition for job number 14144:

BEGIN
    DBMS_JOB.WHAT(14144, 
          'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',
          ''HR'', ''DEPARTMENTS'', 
          ''ESTIMATE'', NULL, 50);');
    END;
    /

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td> 
    <hr> <a rel="nofollow"></a><font><strong>Note:</strong></font> <a rel="nofollow"></a> <p>When you execute the procedure <code>DBMS_JOB.WHAT</code>, Oracle records your current environment. This becomes the new environment for the job.</p> 
    <hr> </td> 
  </tr> 
 </tbody> 
</table>

#### NEXT\_DATE ####

You can alter the next execution time for a job by calling the `DBMS_JOB.NEXT_DATE` procedure, as shown in the following example:

BEGIN
    DBMS_JOB.NEXT_DATE(14144, SYSDATE + 4);
    END;
    /

#### INTERVAL ####

The following example illustrates changing the execution interval for a job by calling the `DBMS_JOB.INTERVAL` procedure:

BEGIN
    DBMS_JOB.INTERVAL(14144, 'NULL');
    END;
    /

In this case, the job will not run again after it successfully executes and it will be deleted from the job queue.

### Broken Jobs ###

A job is labeled as either broken or not broken. Oracle does not attempt to run broken jobs. However, you can force a broken job to run by calling the procedure `DBMS_JOB.RUN.`

#### How a Job Becomes Broken ####

When you submit a job it is considered not broken.

There are two ways a job can break:

*  Oracle has failed to successfully execute the job after 16 attempts.
 *  You have marked the job as broken, using the procedure `DBMS_JOB.BROKEN`:
    
        BEGIN
        DBMS_JOB.BROKEN(14144, TRUE);
        END;
        /

Once a job has been marked as broken, Oracle will not attempt to execute the job until you either mark the job as not broken, or force the job to be executed by calling the procedure `DBMS_JOB.RUN`.

The following example marks job 14144 as not broken and sets its next execution date to the following Monday:

BEGIN
    DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
    END;
    /

##### Restriction: #####

*  You can mark as broken only jobs that you own. If you call `DBMS_JOB.BROKEN` for a job that you do not own, you receive a message stating that the job is not in the job queue.

#### Running Broken Jobs ####

If a problem has caused a job to fail 16 times, Oracle marks the job as broken. Once you have fixed this problem, you can run the job by either:

*  Forcing the job to run by calling `DBMS_JOB.RUN`
 *  Marking the job as not broken by calling `DBMS_JOB.BROKEN` and waiting for Oracle to execute the job

If you force the job to run by calling the procedure `DBMS_JOB.RUN`, Oracle runs the job immediately. If the job succeeds, then Oracle labels the job as not broken and resets its count of the number of failed executions for the job to zero.

Once you reset a job's broken flag (by calling either `RUN` or `BROKEN`), job execution resumes according to the scheduled execution intervals set for the job.

### Forcing a Job to Execute ###

There may be times when you would like to manually execute a job. For example, if you have fixed a broken job, you may want to test the job immediately by forcing it to execute. To force a job to execute immediately, use the procedure `RUN` in the `DBMS_JOB` package.

When you run a job using `DBMS_JOB.RUN`, Oracle recomputes the next execution date. For example, if you create a job on a Monday with a `NEXT_DATE` value of `SYSDATE` and an `INTERVAL` value of `'SYSDATE + 7'`, the job is run every 7 days starting on Monday. However, if you execute `RUN` on Wednesday, the next execution date will be set to the next Wednesday.

The following statement runs job 14144 in your session and recomputes the next execution date:

BEGIN
    DBMS_JOB.RUN(14144);
    END;
    /

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td> 
    <hr> <a rel="nofollow"></a><font><strong>Note:</strong></font> <a rel="nofollow"></a> <p>When you force a job to run, the job is executed in your current session. Running the job reinitializes your session's packages.</p> 
    <hr> </td> 
  </tr> 
 </tbody> 
</table>

##### Restrictions: #####

*  You can only run jobs that you own. If you try to run a job that you do not own, you receive a message that states the job is not in the job queue.
 *  The procedure `RUN` contains an implicit commit. Once you execute a job using `RUN`, you cannot roll back.

### Terminating a Job ###

You can terminate a running job by marking the job as broken, identifying the session running the job, and disconnecting that session. You should mark the job as broken, so that Oracle does not attempt to run the job again.

After you have identified the session running the job (using `V$SESSION` or `V$LOCK`, as shown earlier), you can disconnect the session using the SQL statement `ALTER SYSTEM`. For examples of viewing information about jobs and sessions, see the next section, ["Viewing Job Queue Information"][Viewing Job Queue Information].

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td><a rel="nofollow"></a><font><strong>See Also:</strong></font> <a rel="nofollow"></a> 
    <ul> 
     <li><a rel="nofollow"></a><em><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/toc.htm" rel="nofollow"><u><font>Oracle9i Database Reference</font></u></a></em> for more information on <code>V$SESSION</code> </li> 
     <li><a rel="nofollow"></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/manproc.htm#23659" rel="nofollow"><u><font>"Terminating Sessions"</font></u></a><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#768" rel="nofollow"></a> </li> 
    </ul></td> 
  </tr> 
 </tbody> 
</table>

## Viewing Job Queue Information ##

You can view information about jobs in the job queue using the data dictionary views listed below:

<table title=""> 
 <thead> 
  <tr> 
   <th align="left"><a rel="nofollow"></a><font><strong>View</strong></font></th> 
   <th align="left"><a rel="nofollow"></a><font><strong>Description</strong></font></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>DBA_JOBS</code> <a rel="nofollow"></a></p> <p><code>ALL_JOBS</code> <a rel="nofollow"></a></p> <p><code>USER_JOBS</code></p></td> 
   <td><a rel="nofollow"></a> <p><code>DBA</code> view describes all the jobs in the database. <code>ALL</code> view describes all jobs that are accessible to the current user. <code>USER</code> view describes all jobs owned by the current user.</p></td> 
  </tr> 
  <tr> 
   <td><a rel="nofollow"></a> <p><code>DBA_JOBS_RUNNING</code></p></td> 
   <td><a rel="nofollow"></a> <p>Lists all jobs in the database that are currently running. This view can be joined with <code>V$LOCK</code> to identify jobs that have locks.</p></td> 
  </tr> 
 </tbody> 
</table>

### Displaying Information About a Job ###

The following query creates a listing of the job number, next execution time, failure count, and broken status for each job you have submitted:

SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
       FROM DBA_JOBS;
    
    JOB      NEXT_DATE  NEXT_SEC  FAILURES  B
    -------  ---------  --------  --------  -
       9125  01-JUN-01  00:00:00         4  N
      14144  24-OCT-01  16:35:35         0  N
       9127  01-JUN-01  00:00:00        16  Y
    3 rows selected.

### Displaying Information About Running Jobs ###

You can also display information about only the jobs currently running. The following query lists the session identifier, job number, user who submitted the job, and the start times for all currently running jobs:

SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC
      FROM DBA_JOBS_RUNNING r, DBA_JOBS j
      WHERE r.JOB = j.JOB;
    
    SID        JOB        LOG_USER      THIS_DATE  THIS_SEC
    -----  ----------   -------------   ---------  --------
       12       14144   HR              24-OCT-94  17:21:24
       25        8536   QS              24-OCT-94  16:45:12
    2 rows selected.

<table title="This is a layout table to format a note"> 
 <tbody> 
  <tr> 
   <td><a rel="nofollow"></a><font><strong>See Also:</strong></font> <a rel="nofollow"></a> <p><em><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/toc.htm" rel="nofollow"><u><font>Oracle9i Database Reference</font></u></a></em> for more information on data dictionary views</p></td> 
  </tr> 
 </tbody> 
</table>

--------------------

<table> 
 <tbody> 
  <tr> 
   <td> 
    <table> 
     <tbody> 
      <tr> 
       <td align="middle"><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/logminer.htm" rel="nofollow"><img alt="Go to previous page" src="http://download.oracle.com/docs/cd/B10501_01/dcommon/gifs/larrow.gif"></a></td> 
       <td align="middle"><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/tspaces.htm" rel="nofollow"><img alt="Go to next page" src="http://download.oracle.com/docs/cd/B10501_01/dcommon/gifs/rarrow.gif"></a></td> 
      </tr> 
     </tbody> 
    </table></td> 
   <td align="middle"><a href="http://download.oracle.com/docs/cd/B10501_01/dcommon/html/cpyr.htm" rel="nofollow"><img alt="Oracle" src="http://download.oracle.com/docs/cd/B10501_01/dcommon/gifs/oracle.gif"><br><font><u>Copyright © 2001, 2002 Oracle Corporation.</u></font></a><br><font>All Rights Reserved.</font></td> 
  </tr> 
 </tbody> 
</table>

From:http://download.oracle.com/docs/cd/B10501\_01/server.920/a96521/jobq.htm

[Enabling Processes Used for Executing Jobs]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#768
[Managing Job Queues]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#8312
[Viewing Job Queue Information]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1227
[The DBMS_JOB Package]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#9499
[Submitting a Job to the Job Queue]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#7997
[How Jobs Execute]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1001
[Removing a Job from the Job Queue]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1057
[Altering a Job]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1079
[Broken Jobs]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1129
[Forcing a Job to Execute]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#2051
[Terminating a Job]: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm#1207