Section 8.2. Managing Jobs

8.2. Managing Jobs

The job is the fundamental unit of work in scheduling. This section describes how you can create and manage jobs in the database using the CREATE_JOB procedure in the DBMS_SCHEDULER package. I'll start with a simple example that illustrates job scheduling concepts.

8.2.1. A Simple Example

Suppose that I am working with a banking application and want to schedule the execution of a job that applies interest daily to the account balances. A stored procedure called apply_interest is used to apply the interest. I can use DBMS_SCHEDULER's CREATE_JOB program to create a scheduler job named "apply_daily_interest" as follows:

       1  BEGIN       2     DBMS_SCHEDULER.create_job (job_name => 'apply_daily_interest',       3                   job_type              => 'STORED_PROCEDURE',       4                   job_action            => 'apply_interest',       5                   repeat_interval       => 'FREQ=DAILY; INTERVAL=1',       6                   enabled               => TRUE,       7                   comments              => 'Apply Daily Interest'       8                  );       9* END; 

Let's examine the lines in detail in the following table.

Line	Description
2	I define the name of the job. The name must be a valid Oracle identifier, which means that it must start with a letter; contain some combination of letters, numbers, #, _, or $; and cannot be longer than 30 characters. The name also must be unique in the Oracle namespace; it cannot, for example, have the same name as a table.
3	Here I specify what type of program is to be run. This is a wonderful enhancement of DBMS_SCHEDULER over the functionality available in DBMS_JOB. In this case, the program to be executed (apply_interest) is a stored procedure, indicated by the value ("STORED_PROCEDURE") of the job_type parameter. Other valid values for this argument are "PLSQL_BLOCK" and "EXECUTABLE".
4	The job_action parameter indicates what this job doesin this case, it executes the stored procedure apply_interest.
5	The repeat_interval parameter indicates how often this job should be run, in this case every day. (I'll explore the use of intervals in more detail later in this chapter.)
6	The value (TRUE) of the enabled parameter is the default, which means that the job is immediately enabled and run.
7	Here I can specify optional comments for the job, which may later help explain what a job does.

Once you have created a job, you will be able to find information about it in the data dictionary view DBA_SCHEDULER_JOBS. Let's take a quick look now at that view to confirm the results of the call to CREATE_JOB above; I'll explore it in more detail below.

     SELECT owner, job_name, job_type          , job_action, repeat_interval, comments       FROM dba_scheduler_jobs 

The following shows the output in a vertical format for easy viewing, the column names on the left and their contents on the right.

     OWNER                         : ARUP     JOB_NAME                      : APPLY_DAILY_INTEREST     JOB_TYPE                      : STORED_PROCEDURE     JOB_ACTION                    : apply_interest     REPEAT_INTERVAL               : FREQ=DAILY; INTERVAL=1     COMMENTS                      : Apply Daily Interest 

I have now created a job that calls a stored procedure. Although there are some syntactical differences, the core functionality will at first glance appear quite similar to that offered by DBMS_JOB. After reading the following sections, however, you will understand more clearly how much more DBMS_SCHEDULING does for us.

8.2.1.1. Running OS executables and anonymous blocks

Suppose that the job you want to schedule involves a Unix script, not a PL/SQL program. A common example is an RMAN incremental backup . The full path for the RMAN script is u01/app/oracle/admin/tools/kick_rman_inc.sh. As a Unix executable, the script is not entirely within the purview of the Oracle database. Do you have to resort to a cron job to schedule it? As discussed earlier, a drawback of operating system scheduling is that it will run even when the database is down, and in this case you want the job to run only when the database is up. A compelling advantage of DBMS_SCHEDULER over DBMS_JOB is its ability to directly specify and execute non-database programs from within the database context.

The following example uses the CREATE_JOB procedure in the DBMS_SCHEDULER package to schedule the RMAN incremental backup.

       1  BEGIN       2     DBMS_SCHEDULER.create_job       3        (job_name        => 'RMAN_INC',       4        repeat_interval  => 'FREQ=DAILY; BYHOUR=2',       5        job_type         => 'EXECUTABLE',       6        job_action       => '/u01/app/oracle/admin/tools/kick_rman_inc.sh',       7        enabled          => TRUE,       8        comments         => 'Take RMAN Inc Backup'       9        );      10* END; 

This job will call the Unix executable kick_rman_inc.sh. Note that in line 5 the job_type parameter is set to EXECUTABLE.^[*]

^[*] By contrast, in the first example, it was set to STORED_PROCEDURE.

You can use the CREATE_JOB procedure to create jobs that call anonymous PL/SQL code segmentsfor example:

     BEGIN        DBMS_SCHEDULER.create_job                (job_name             => 'RMAN_INC',                 repeat_interval      => 'FREQ=DAILY; BYHOUR=2',                 job_type             => 'PLSQL_BLOCK',                 job_action           => 'DECLARE i number; BEGIN code; END;',                 enabled              => TRUE,                 comments             => 'Take RMAN Inc Backup'                 );     END; 

Here the parameter job_action specifies the whole PL/SQL code segment: a DECLARE-BEGIN-END block terminated by a semicolon. You can include any valid anonymous PL/SQL block in the placeholder marked code in the above example.

8.2.1.2. DBA_SCHEDULER_JOBS view

After you have created a job, you can check it in the data dictionary view DBA_SCHEDULER_JOBS. Here are some key columns of the view. Appendix A shows the complete set of columns.

OWNER

Owner of the job.

JOB_NAME

Name of the job.

CLIENT_ID

If the user specified the client identifier for the session while creating a job, it is recorded here. You can set the client identifier by calling DBMS_SESSION.SET_IDENTIFIER.

GLOBAL_UID

If the user is a global (or enterprise) user, the global user id is recorded here.

JOB_TYPE

The type of job; valid values are EXECUTABLE, PLSQL_BLOCK, and STORED_PROCEDURE.

JOB_ACTION

What the job does. If it is a PL/SQL code segment, then the entire segment is shown here. If it is an executable or a stored procedure, its name is recorded here.

START_DATE

Start time of the job in the TIMESTAMP datatype.

REPEAT_INTERVAL

Calendar string that specifies the schedule of the job (e.g., FREQ=DAILY; BYHOUR=2). (See also the later section "Calendar Strings.")

ENABLED

Whether the job is enabled (TRUE or FALSE).

STATE

Current state this job is in (e.g., SCHEDULED, RUNNING, SUCCEEDED, FAILED).

RUN_COUNT

Number of times this job has been run.

FAILURE_COUNT

Number of times this job has failed.

RETRY_COUNT

If the job failed, it is retried; this column shows how many times it has been retried.

LAST_START_DATE

Timestamp of the last time the job started.

LAST_RUN_DURATION

Duration of the last time the job ran.

NEXT_RUN_DATE

Next time the job is scheduled to run.

SYSTEM

Whether a job is a system job (TRUE or FALSE).

COMMENTS

Comments entered by you earlier.

I'll discuss additional columns as we describe other aspects of DBMS_SCHEDULER functionality.

8.2.2. Simple Job Management

Now that you understand basically how to create a job, let's see how to manage the job we just created. Unless otherwise stated, all tasks are performed by various programs in the DBMS_SCHEDULER package.

8.2.2.1. Enabling and disabling jobs

You can temporarily disable or enable a job using the DISABLE and ENABLE procedures, respectively. For example, to disable the job RMAN_INC, specify:

     BEGIN         DBMS_SCHEDULER.disable (NAME => 'RMAN_INC');     END; 

If this job is currently running, the above command will return an error. You can include the FORCE parameter to override the default behavior:

     BEGIN         DBMS_SCHEDULER.disable (NAME => 'RMAN_INC', FORCE=> TRUE);     END; 

Specifying FORCE will disable the job, but will allow the currently running process to complete.

You can enable a currently disabled job by calling the procedure ENABLE as follows.

     BEGIN        DBMS_SCHEDULER.enable (NAME => 'RMAN_INC');     END; 

When you create a job and do not set the enabled parameter, the job is created but is disabled. You must explicitly enable it.

8.2.2.2. Stopping running jobs

When a job is running and you want to stop it, you can run the STOP_JOB procedure as follows.

     BEGIN        DBMS_SCHEDULER.stop_job (JOB_NAME => 'RMAN_INC');     END; 

STOP_JOB will attempt to gracefully stop a job. There are times, unfortunately, when a graceful shutdown is not possible, in which case the above statement returns an error. You can force a job to shut down by using the FORCE parameter. For example, to stop the job APPLY_INTEREST (which executes a stored procedure), created earlier in this chapter, I can run the following block to immediately shut down the job, even if it is currently running.

     BEGIN        DBMS_SCHEDULER.stop_job (job_name      => 'APPLY_INTEREST',                                 FORCE         => TRUE);     END; 

The owner of a job can shut that job down normally without requiring the FORCE=>TRUE parameter. Any other user with ALTER privileges on that job can also stop it normally. For example, the owner of the job (ACC_MASTER) can grant the privilege to the user ARUP as follows:

     GRANT ALTER ON apply_daily_interest TO arup; 

Now ARUP will be able to stop the job.

To use the FORCE parameter in your call to DBMS_SCHEDULER.STOP_JOB, you must have the MANAGE SCHEDULER system privilege .

Only jobs that run PL/SQL code segments and stored procedures can be stopped by setting the FORCE parameter. Jobs that run operating system executables cannot be stopped with the FORCE parameter. You will need to wait until they have completed.

8.2.2.3. Running a job

The Scheduler allows you to explicitly request the running of a job. There are a number of circumstances requiring manual execution of a job:

• You stopped a job to take care of an issue, and now you want to run that job to completion.

• The job is not yet scheduled to run, but you decide that you need to run it immediately anyway.

• The job is scheduled to run now, but it is failing, and you want to see the reason. (In other words, you run the job to debug the job.)

You can run a job using the RUN_JOB procedure . For example, to run the job CALCULATE_DAILY_INTEREST, you would specify:

     BEGIN        DBMS_SCHEDULER.run_job (job_name => 'CALCULATE_DAILY_INTEREST');     END; 

This will cause the job to be run in the session to which you are currently connected. To do this, you must

• Be connected as the owner of the job or

• Have the system privilege ALTER ANY JOB

RUN_JOB is a very helpful program. If the job fails for some reason, you will find out immediately; you won't have to wait to examine the job log at a later time. (The job log is described later in the "Managing Logging" section.) Even if the job is currently running as scheduled or if someone else is also running it, RUN_JOB still lets you run it immediately in your session. Running a job this way will help you to determine any problems with the job's execution or scheduling because if it fails, information about it will display right away in your execution environment.

When you call DBMS_SCHEDULER.RUN_JOB, the columns RUN_COUNT, LAST_START_DATE, LAST_RUN_DURATION, and FAILURE_COUNT in the data dictionary view DBA_SCHEDULER_JOBS are not updated. There will, in other words, be no record of that job's execution in the metadata for the job.

Suppose that you want to start a job, but it will take five or six hours to complete. You are likely, then, to want to run the job in the background, outside of the current session. You can do this by setting the use_current_session parameter in the RUN_JOB procedure to FALSE. In the example below, I schedule CALCULATE_DAILY_INTEREST to run as a task in the background, and control is immediately returned to my session.

     BEGIN        DBMS_SCHEDULER.run_job (            job_name               => 'CALCULATE_DAILY_INTEREST',             use_current_session   => FALSE             );     END; 

If the program runs successfully, it will also update the columns RUN_COUNT, LAST_START_DATE, LAST_RUN_DURATION, and FAILURE_COUNT in the data dictionary view DBA_SCHEDULER_JOBS. However, if the job fails for some reason, the failure will not show immediately on the screen, but instead will show up in the job logs, described later in the "Managing Logging" section.

8.2.2.4. Dropping a job

When you no longer need a job, you can drop it altogether using the procedure DROP_JOB. For example, to drop the job APPLY_DAILY_INTEREST, you would specify:

     BEGIN        DBMS_SCHEDULER.drop_job (job_name => 'APPLY_DAILY_INTEREST');     END; 

If the job is running at the time, then this code will return an error. If you include the FORCE parameter, the Scheduler will first attempt to stop the job and will then remove it:

     BEGIN        DBMS_SCHEDULER.drop_job (job_name      => 'APPLY_DAILY_INTEREST',                                 FORCE         => TRUE);     END;