Jobs

A job object (job) is a collection of metadata that describes a user-defined task that is scheduled to run one or more times. It is a combination of what needs to be executed (the action) and when (the schedule).

You specify the job action in one of the following ways:

  • By specifying as a job attribute the database program unit or external executable to run. This is known as specifying the job action inline.

  • By specifying as a job attribute the name of an existing program object (program), where the program specifies the database program unit or external executable to run.

    The job owner must have the EXECUTE privilege on the program or the EXECUTE ANY PROGRAM system privilege.

You specify the job schedule in one of the following ways:

  • By setting attributes of the job object to define start and end dates and repeat interval. This is known as specifying the schedule inline.

  • By specifying as a job attribute the name of an existing schedule object (schedule), where the schedule defines start and end dates and repeat interval.

After you create a job and enable it, the Scheduler automatically runs the job according to its schedule. You can view a job's run status and its job log by querying data dictionary views.

This section contains:

See Also:

Job Instances

A job instance represents a specific run of a job. Jobs that are scheduled to run only once will have only one instance. Jobs that have a repeating schedule will have multiple instances, with each run of the job representing an instance. For example, a job that is scheduled to run on Tuesday, Oct. 8th 2002 will have one instance. A job that runs daily at noon for a week has seven instances, one for each time the job runs.

When a job is created, only one entry is added to the Scheduler's job table to represent the job. Depending on the logging level set, each time the job runs, an entry is added to the job log. Therefore, if you create a job that has a repeating schedule, you will find one entry in the job views and multiple entries in the job log. Each job instance log entry provides information about a particular run, such as the job completion status and the start and end time. Each run of the job is assigned a unique log id which is used in both the job log and job run details views.

See "Scheduler Data Dictionary Views" for more information.

Job Arguments

When a job references a program object (program), you can supply job arguments to override the default program argument values, or provide values for program arguments that have no default value. You can also provide argument values to an inline action (for example, a stored procedure) that the job specifies.

A job cannot be enabled until all required program argument values are defined, either as defaults in a referenced program object, or as job arguments.

A common example of a job is one that runs a set of nightly reports. If different departments require different reports, you can create a program for this task that can be shared among different users from different departments. The program action would be to run a reports script, and the program would have one argument: the department number. Each user can then create a job that points to this program, and can specify the department number as a job argument.

See Also:

How Programs, Jobs, and Schedules are Related

To define what is executed and when, you assign relationships among programs, jobs, and schedules. Figure 26-1 illustrates examples of such relationships.

Figure 26-1 Relationships Among Programs, Jobs, and Schedules

Description of Figure 26-1 follows
Description of "Figure 26-1 Relationships Among Programs, Jobs, and Schedules"

To understand Figure 26-1, consider a situation where tables are being analyzed. In this example, P1 would be a program to analyze a table using the DBMS_STATS package. The program has an input parameter for the table name. Two jobs, J1 and J2, both point to the same program, but each supplies a different table name. Additionally, schedule S1 could specify a run time of 2:00 a.m. every day. The end result would be that the two tables named in J1 and J2 are analyzed daily at 2:00 a.m.

Note that J4 points to no other entity, so it is self-contained with all relevant information defined in the job itself. P2, P9 and S2 illustrate that you can leave a program or schedule unassigned if you want. You could, for example, create a program that calculates a year-end inventory and temporarily leave it unassigned to any job.

Job Categories

The Scheduler supports the following types of jobs:

Database Jobs

Database jobs run Oracle Database program units, including PL/SQL anonymous blocks, PL/SQL stored procedures, and Java stored procedures. For a database job where the action is specified inline, job_type is set to 'PLSQL_BLOCK' or 'STORED_PROCEDURE', and job_action contains either the text of a PL/SQL anonymous block or the name of a stored procedure. (If a program object is named instead of specifying the action inline, the corresponding program_type and program_action would be set accordingly.)

Chain Jobs

The chain is the Scheduler mechanism that enables dependency-based scheduling. In its simplest form, it defines a group of program objects and the dependencies among them. A job can point to a chain instead of pointing to a single program object. The job then serves to start the chain. For a chain job, job_type is set to 'CHAIN'.

See "Chains" for more information.

External Jobs

External jobs run external executables. An external executable is an operating system executable that runs outside the database. For an external job, job_type is specified as 'EXECUTABLE'. (If using named programs, the corresponding program_type would be 'EXECUTABLE'.) The job_action (or corresponding program_action if using named programs) is the full operating system–dependent path of the desired external executable, excluding any command line arguments. An example might be /usr/local/bin/perl or C:\perl\bin\perl. The program or job arguments for type 'EXECUTABLE' must be a string type such as CHAR or VARCHAR2.

Note that a Windows batch file is not directly executable and must be run with cmd.exe.

Like any Scheduler job, you can assign a schema when you create the job. That schema then becomes the job owner. Note that although it is possible to create an external job in the SYS schema, Oracle recommends against this practice.

The CREATE JOB and CREATE EXTERNAL JOB privileges are both required for any schema that runs external jobs.

External executables must run as some operating system user. Thus, the Scheduler enables you to assign operating system credentials to any external job that you create. You do so with a database object introduced in Oracle Database 11g Release 1 called a credential.

There are two types of external jobs: local external jobs and remote external jobs. A local external job runs its external executable on the same computer as the database that schedules the job. A remote external job runs its executable on a remote host—that is, on a host computer other than the computer running the database that schedules the job. The remote host does not need to have an Oracle database.

Credentials, local external jobs, and remote external jobs are discussed in detail in the following sections:

See Also:

"Creating External Jobs"

About Credentials

A credential is a host user name and password pair stored in a dedicated database object. You set the credential_name attribute of an external job to designate a credential for that job. The job's external executable then runs with the user name and password specified by that credential.

You use the DBMS_SCHEDULER.CREATE_CREDENTIAL procedure to create a credential. You must have the CREATE JOB privilege to create a credential in your own schema, and the CREATE ANY JOB privilege to create a credential in any schema except SYS. A credential can be used only by a job whose owner has EXECUTE privileges on the credential or whose owner is also the owner of the credential. Because a credential belongs to a schema like any other schema object, you use the GRANT SQL statement to grant privileges on a credential.

BEGIN
 DBMS_SCHEDULER.CREATE_CREDENTIAL('HRCREDENTIAL', 'hruser', 'hr001515');
END;

GRANT EXECUTE ON HRCREDENTIAL to HR;

You can query the *_SCHEDULER_CREDENTIALS views to see a list of credentials in the database. Credential passwords are stored obfuscated, and are not displayed in the *_SCHEDULER_CREDENTIALS views.

About Local External Jobs

A local external job runs its external executable on the same computer as the Oracle database that schedules the job. For such a job, the destination job attribute is null or contains a value of localhost.

Local external jobs write stdout and stderr output to log files in the directory ORACLE_HOME/scheduler/log. You can retrieve the contents of these files with DBMS_SCHEDULER.GET_FILE.

You do not have to assign a credential to a local external job, although Oracle strongly recommends that you do so for improved security. If you do not assign a credential, the job runs with default credentials. Table 26-1 shows the default credentials for different platforms and different job owners.

Table 26-1 Default Credentials for Local External Jobs

Job in SYS Schema? Platform Default Credentials

Yes

All

User who installed Oracle Database

No

UNIX and Linux

Values of the run-user and run-group attributes specified in the file ORACLE_HOME/rdbms/admin/externaljob.ora

No

Windows

User that the OracleJobSchedulerSID Windows service runs as (either the Local System account or a named local or domain user).

Note: You must manually enable and start this service. For improved security, Oracle recommends using a named user instead of the Local System account.

Note:

Default credentials are included for compatibility with previous releases of Oracle Database, and may be deprecated in a future release. It is therefore best to assign a credential to every local external job.

To disable the running of local external jobs that were not assigned credentials, remove the run_user attribute from the ORACLE_HOME/rdbms/admin/externaljob.ora file (UNIX and Linux) or stop the OracleJobScheduler service (Windows). Note that these steps do not disable the running of local external jobs in the SYS schema.

See Also:

About Remote External Jobs

A remote external job runs its external executable on a host computer other than the computer running the Oracle database that schedules the job. For purposes of this discussion, the computer that the remote executable runs on is referred to as the remote host. The remote host may or may not have Oracle Database installed. However, in all cases, the remote host has a Scheduler agent that the database communicates with to start external executables on the remote host. The agent is also involved in returning execution results to the database. The agent is an executable on a remote host that is installed separately. It listens on a network port for incoming job requests and executes them.

When creating a remote external job, you specify a remote host and port number as the destination attribute of the job. In addition, you must specify a credential for a remote external job.

Remote external jobs write stdout and stderr output to log files in the directory AGENT_HOME/data/log. You can retrieve the contents of these files with DBMS_SCHEDULER.GET_FILE. Example 27-6, "Creating a Local External Job and Retrieving stdout" illustrates how to retrieve stdout output. Although this example is for a local external job, the method is the same for remote external jobs.

See Also:

Detached Jobs

You use a detached job to start a script or application that runs in a separate process, independently and asynchronously to the Scheduler. A detached job typically starts another process and then exits. Upon exit (when the job action is completed) a detached job remains in the running state. The running state indicates that the asynchronous process that the job started is still active. When the asynchronous process finishes its work, it must connect to the database and call DBMS_SCHEDULER.END_DETACHED_JOB_RUN, which ends the job.

A job is detached if it points to a program object (program) that has its detached attribute set to TRUE (a detached program).

You use a detached job under the following two circumstances:

  • When it is impractical to wait for the launched asynchronous process to complete, as this would hold resources unnecessarily.

    An example is sending a request to an asynchronous Web service. It could take hours or days for the Web service to respond, and you do not want to hold a Scheduler job slave while waiting for the response. (See "Scheduler Architecture" for information about job slaves.)

  • When it is impossible to wait for the launched asynchronous process to complete, because the process shuts down the database.

    An example would be using a Scheduler job to launch an RMAN script that shuts down the database, makes a cold backup, and then restarts the database. See Example 27-7.

A detached job works as follows:

  1. When it is time for the job to start, the job coordinator assigns a job slave to the job, and the job slave runs the program action defined in the detached program. The program action can be a PL/SQL block, a stored procedure, or an external executable.

  2. The program action performs an immediate-return call of another script or executable, referred to here as Process A, and then exits. Because the work of the program action is complete, the job slave exits, but leaves the job in a running state.

  3. Process A performs its processing. If it runs any DML against the database, it must commit its work. When processing is complete, Process A logs in to the database and calls END_DETACHED_JOB_RUN.

  4. The detached job is logged as completed.

You can also call STOP_JOB to end a running detached job.

See Also:

"Creating Detached Jobs" for an example of performing a cold backup of the database with a detached job

Lightweight Jobs

Use lightweight jobs when you have many short-duration jobs that run frequently. Under certain circumstances, using lightweight jobs can deliver a small performance gain.

Lightweight jobs have the following characteristics:

  • Unlike regular jobs, they are not schema objects.

  • They have a significant improvement in create and drop time over regular jobs because they do not have the overhead of creating a schema object.

  • They have lower average session creation time than regular jobs.

  • They have a small footprint on disk for job metadata and runtime data.

You designate a lightweight job by setting the job_style job attribute to 'LIGHTWEIGHT'. The other job style is 'REGULAR', which is the default.

Like programs and schedules, regular jobs are schema objects. In releases before Oracle Database 11g Release 1, regular jobs were the only job style supported by the Scheduler.

A regular job offers the maximum flexibility but does entail some overhead when it is created or dropped. The user has fine-grained control of the privileges on the job, and the job can have as its action a program or a stored procedure owned by another user.

If a relatively small number of jobs that run infrequently need to be created, then regular jobs are preferred over lightweight jobs.

A lightweight job must reference a program object (program) to specify a job action. The program must be already enabled when the lightweight job is created, and the program type must be either 'PLSQL_BLOCK' or 'STORED_PROCEDURE'. Because lightweight jobs are not schema objects, you cannot grant privileges on them. A lightweight job inherits privileges from its specified program. Thus, any user who has a certain set of privileges on the program has corresponding privileges on the lightweight job.

See Also:

"Creating Jobs" and "Examples of Using the Scheduler" for examples of creating lightweight jobs