Using Jobs

A job is the combination of a schedule and a program, along with any additional arguments required by the program. This section introduces you to basic job tasks, and discusses the following topics:

See Also:

"Jobs" for an overview of jobs.

Job Tasks and Their Procedures

Table 27-1 illustrates common job tasks and their appropriate procedures and privileges:

Table 27-1 Job Tasks and Their Procedures

Task Procedure Privilege Needed

Create a job

CREATE_JOB or CREATE_JOBS

CREATE JOB or CREATE ANY JOB

Alter a job

SET_ATTRIBUTE or SET_JOB_ATTRIBUTES

ALTER or CREATE ANY JOB or be the owner

Run a job

RUN_JOB

ALTER or CREATE ANY JOB or be the owner

Copy a job

COPY_JOB

ALTER or CREATE ANY JOB or be the owner

Drop a job

DROP_JOB

ALTER or CREATE ANY JOB or be the owner

Stop a job

STOP_JOB

ALTER or CREATE ANY JOB or be the owner

Disable a job

DISABLE

ALTER or CREATE ANY JOB or be the owner

Enable a job

ENABLE

ALTER or CREATE ANY JOB or be the owner


See "Scheduler Privileges" for further information regarding privileges.

Creating Jobs

This section contains:

Overview of Creating Jobs

You create one or more jobs using the CREATE_JOB or CREATE_JOBS procedures or Enterprise Manager. The CREATE_JOB procedure is used to create a single job. This procedure is overloaded to enable you to create different types of jobs that are based on different objects. Multiple jobs can be created in a single transaction using the CREATE_JOBS procedure.

For each job being created, you specify a job type, an action, a schedule, an optional job class, and other attributes. Jobs are created disabled by default and need to be enabled with DBMS_SCHEDULER.ENABLE to run. As soon as you enable a job, it is automatically run by the Scheduler at its next scheduled date and time. You can also set the enabled argument of the CREATE_JOB procedure to TRUE, in which case the job is ready to be automatically run according to its schedule as soon as you create it.

Example 27-1 demonstrates creating a single job called update_sales, which calls a stored procedure in the OPS schema that updates a sales summary table:

Example 27-1 Creating a Job

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'update_sales',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
   start_date         =>  '28-APR-08 07.00.00 PM Australia/Sydney',
   repeat_interval    =>  'FREQ=DAILY;INTERVAL=2', /* every other day */
   end_date           =>  '20-NOV-08 07.00.00 PM Australia/Sydney',
   job_class          =>  'batch_update_jobs',
   comments           =>  'My new job');
END;
/

You can create a job in another schema by specifying schema.job_name. The creator of a job is, therefore, not necessarily the job owner. The job owner is the user in whose schema the job is created. Jobs are executed with the privileges of the schema in which the job is created. The NLS environment of the job when it runs is that which was present at the time the job was created.

After a job is created, it can be queried using the *_SCHEDULER_JOBS views.

Jobs are set to be automatically dropped by default after they complete. Setting the auto_drop attribute to FALSE causes the job to persist. Note that repeating jobs are not auto-dropped unless the job end date passes, the maximum number of runs (max_runs) is reached, or the maximum number of failures is reached (max_failures).

Specifying a Job Action and Job Schedule

Because the CREATE_JOB procedure is overloaded, there are several different ways of using it. In addition to specifying the job action and job repeat interval as job attributes as shown in Example 27-1—this is known as specifying the job action and job schedule inline—you can create a job that points to a program object (program) to specify the job action, points to a schedule object (schedule) to specify the repeat interval, or points to both a program and schedule. This is discussed in the following sections:

Creating Jobs Using a Named Program

You can create a job by pointing to a named program instead of inlining its action. To create a job using a named program, you specify the value for program_name in the CREATE_JOB procedure when creating the job and do not specify the values for job_type, job_action, and number_of_arguments.

To use an existing program when creating a job, the owner of the job must be the owner of the program or have EXECUTE privileges on it. An example of using the CREATE_JOB procedure with a named program is the following PL/SQL block, which creates a regular job called my_new_job1:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name          =>  'my_new_job1',
   program_name      =>  'my_saved_program', 
   repeat_interval   =>  'FREQ=DAILY;BYHOUR=12',
   comments          =>  'Daily at noon');
END;
/

The following PL/SQL block creates a lightweight job. Lightweight jobs must reference a program, and the program type must be 'PLSQL_BLOCK' or 'STORED_PROCEDURE'. In addition, the program must be already enabled when you create the job.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name         =>  'my_lightweight_job1',
   program_name     =>  'polling_prog_n2',
   repeat_interval  =>  'FREQ=SECONDLY;INTERVAL=10',
   end_date         =>  '30-APR-09 04.00.00 AM Australia/Sydney',
   job_style        => 'LIGHTWEIGHT',
   comments         => 'Job that polls device n2 every 10 seconds');
END;
/

Creating Jobs Using a Named Schedule

You can also create a job by pointing to a named schedule instead of inlining its schedule. To create a job using a named schedule, you specify the value for schedule_name in the CREATE_JOB procedure when creating the job and do not specify the values for start_date, repeat_interval, and end_date.

You can use any named schedule to create a job because all schedules are created with access to PUBLIC. An example of using the CREATE_JOB procedure with a named schedule is the following statement, which creates a regular job called my_new_job2:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name                 =>  'my_new_job2', 
   job_type                 =>  'PLSQL_BLOCK',
   job_action               =>  'BEGIN SALES_PKG.UPDATE_SALES_SUMMARY; END;',
   schedule_name            =>  'my_saved_schedule');
END;
/

Creating Jobs Using a Named Program and Schedule

A job can also be created by pointing to both a named program and schedule. An example of using the CREATE_JOB procedure with a named program and schedule is the following statement, which creates a regular job called my_new_job3 based on the existing program my_saved_program1 and the existing schedule my_saved_schedule1:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_new_job3', 
   program_name        =>  'my_saved_program1', 
   schedule_name       =>  'my_saved_schedule1');
END;
/

Setting Job Arguments

After creating a job, you may need to set job arguments if:

  • The inline job action is a stored procedure or other executable that requires arguments

  • The job references a named program object and you want to override one or more default program arguments

  • The job references a named program object and one or more of the program arguments were not assigned a default value

To set job arguments, use the SET_JOB_ARGUMENT_VALUE or SET_JOB_ANYDATA_VALUE procedures or Enterprise Manager. SET_JOB_ANYDATA_VALUE is used for complex data types that cannot be represented as a VARCHAR2 string.

An example of a job that might need arguments is one that starts a reporting program that requires a start date and end date. The following code example sets the end date job argument, which is the second argument expected by the reporting program:

BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                => 'ops_reports',
   argument_position       => 2,
   argument_value          => '12-DEC-03');
END;
/

If you use this procedure on an argument whose value has already been set, it will be overwritten. You can set argument values using either the argument name or the argument position. To use argument name, the job must reference a named program object, and the argument must have been assigned a name in the program object. If a program is inlined, only setting by position is supported. Arguments are not supported for jobs of type plsql_block.

To remove a value that has been set, use the RESET_JOB_ARGUMENT procedure. This procedure can be used for both regular and ANYDATA arguments.

See Oracle Database PL/SQL Packages and Types Reference for information about the SET_JOB_ARGUMENT_VALUE and SET_JOB_ANYDATA_VALUE procedures.

Setting Job Attributes

After creating a job, you can set job attributes by using the SET_ATTRIBUTE or SET_JOB_ATTRIBUTES procedures or Enterprise Manager. Although many job attributes can be set during the call to CREATE_JOB, some attributes can be set only with SET_ATTRIBUTE or SET_JOB_ATTRIBUTES after the job is created.

See Oracle Database PL/SQL Packages and Types Reference for information about the SET_ATTRIBUTE and SET_JOB_ATTRIBUTES procedures and about the various job attributes.

Creating External Jobs

Both the CREATE JOB and CREATE EXTERNAL JOB privileges are required to create local or remote external jobs.

To create a local or remote external job:

  1. Ensure that you have performed all required setup tasks for external jobs.

  2. Create the job using the CREATE_JOB procedure of the DBMS_SCHEDULER package.

    Omit the enabled attribute or set it to FALSE.

  3. Create a credential using the CREATE_CREDENTIAL procedure.

    See "About Credentials" for details.

    Note:

    On Windows, the host user that runs the external executable must be assigned the Log on as a batch job logon right.
  4. Set the credential_name attribute of the job using the SET_ATTRIBUTE procedure.

    The job owner must have EXECUTE privileges on the credential or be the credential owner. For remote external jobs, the credential_name attribute is required. If this attribute is not set for local external jobs, default credentials are used. See Table 26-1 for more information.

    Note:

    For improved security, Oracle strongly recommends that you assign credentials to local external jobs.
  5. For remote external jobs only, set the destination attribute of the job using the SET_ATTRIBUTE procedure.

    The attribute must be of the form host:port, where host is the host name or IP address of the remote host, and port is the port on which the Scheduler agent on that host listens. To determine this port number, view the file schagent.conf, which is located in the Scheduler agent home directory on the remote host.

  6. (Optional) For remote external jobs only, use a utility such as nslookup to ensure that the remote host name is valid and the host is accessible.

  7. Enable the job using the ENABLE procedure.

Example 27-2 Creating a Local External Job

This example creates a local external job named CLEANLOGS that uses a credential named LOGOWNER.

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
   job_name             => 'CLEANLOGS',
   job_type             => 'EXECUTABLE',
   job_action           => '/home/logowner/cleanlogs',
   repeat_interval      => 'FREQ=DAILY; BYHOUR=23',
   enabled              => FALSE);
DBMS_SCHEDULER.SET_ATTRIBUTE('CLEANLOGS', 'credential_name', 'LOGOWNER');
DBMS_SCHEDULER.ENABLE('CLEANLOGS');
END;
/

Because you cannot specify credentials when creating the job and instead must specify them with SET_ATTRIBUTE, the job is created disabled to allow time to set credentials. After credentials are set, the job is enabled.

Example 27-3 Creating a Local External Job That Runs a DOS Command

This example demonstrates how to create a local external job on Windows that runs a DOS built-in command (in this case, mkdir). The job runs cmd.exe with the /c option. Default credentials are used.

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
   job_name             => 'MKDIR_JOB',
   job_type             => 'EXECUTABLE',
   number_of_arguments  => 3,
   job_action           => '\windows\system32\cmd.exe',
   auto_drop            => FALSE);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('mkdir_job',1,'/c');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('mkdir_job',2,'mkdir');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('mkdir_job',3,'\temp\extjob_test_dir');
DBMS_SCHEDULER.SET_ATTRIBUTE('MKDIR_JOB', 'credential_name', 'STEVE');
DBMS_SCHEDULER.ENABLE('MKDIR_JOB');
END;
/

Example 27-4 Creating Remote External Jobs for Multiple Remote Hosts

This example creates the same remote external job for multiple remote hosts. The PL/SQL code includes a loop that iterates over the host names. remote_cred is the name of a credential that is valid on all hosts. The list of destinations is a list of host names and Scheduler agent ports. The executable being run on all hosts is the application /u01/app/ext_backup.

The user running this code must have both the CREATE JOB and CREATE EXTERNAL JOB privileges.

declare
job_prefix varchar2(30) := 'remote_';
job_name varchar2(30);
destinations dbms_utility.lname_array;
begin
 
   destinations(1) := 'host1:1234';
   destinations(2) := 'host2:1234';
   destinations(3) := 'host3:1234';
   destinations(4) := 'host4:1234';
 
  for i in 1..destinations.LAST loop
    job_name := dbms_scheduler.generate_job_name(job_prefix);
    dbms_scheduler.create_job(job_name,
    job_type=>'executable',
    job_action=>'/u01/app/ext_backup',
    number_of_arguments=>0,
    enabled=>false);
 
    dbms_scheduler.set_attribute(job_name,'destination',destinations(i));
    dbms_scheduler.set_attribute(job_name,'credential_name','remote_cred');
    dbms_scheduler.enable(job_name);
  end loop;
end;
/

Note the following about this example:

  • Because the jobs have no start date, they run immediately.

  • Because the jobs have no repeat interval, they are dropped upon completion.

  • The jobs are created disabled and are not enabled until job credentials are set.

Example 27-5 Creating a Remote External Job That Submits SQL Statements

This example illustrates how a remote external job can submit SQL statements to a remote Oracle database. The job action runs a shell script that uses SQL*Plus to submit the statements. The script must reside on the remote host. The script, shown below, starts by setting all environment variables required to run SQL*Plus on Linux.

To avoid hard-coding a database password in the script, external authentication is used.

#!/bin/sh

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

# The following command assumes external authentication
$ORACLE_HOME/bin/sqlplus / << EOF
set serveroutput on;
select * from dual;
EXIT;
EOF

Example 27-6 Creating a Local External Job and Retrieving stdout

This example for Linux and UNIX shows how to create and run a local external job and then use the GET_FILE procedure to retrieve the job's stdout output. For local external jobs, stdout output is stored in a log file in ORACLE_HOME/scheduler/log. It is not necessary to supply this path to GET_FILE; you supply only the file name, which you generate by querying the log views for the job's external log ID and then appending "_stdout".

-- User scott must have CREATE JOB and CREATE EXTERNAL JOB privileges
grant create job, create external job to scott ;
 
connect scott/tiger
set serveroutput on
 
-- Create a credential for the job to use
exec dbms_scheduler.create_credential('my_cred','host_username','host_passwd')
 
-- Create a job that lists a directory. After running, the job is dropped.
begin
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'lsdir',
job_type=>'EXECUTABLE',
job_action=>'/bin/ls',
number_of_arguments => 1,
enabled => false,
auto_drop =>true
);
dbms_scheduler.set_job_argument_value('lsdir',1,'/tmp');
dbms_scheduler.set_attribute('lsdir','credential_name','my_cred');
dbms_scheduler.enable('lsdir');
end;
/
 
-- Wait a bit for the job to run, and then check the job results.
select job_name, status, error#, actual_start_date, additional_info
 from user_scheduler_job_run_details where job_name='LSDIR';
 
-- Now use the external log id from the additional_info column to
-- formulate the log file name and retrieve the output
declare
 my_clob clob;
 log_id varchar2(50);
begin
 select regexp_substr(additional_info,'job[_0-9]*') into log_id
   from user_scheduler_job_run_details where job_name='LSDIR';
 dbms_lob.createtemporary(my_clob, false);
 dbms_scheduler.get_file(
   source_file     => log_id ||'_stdout',
   credential_name => 'my_cred',
   file_contents   => my_clob,
   source_host     => null);
 dbms_output.put_line(my_clob);
end;
/

Note:

For a remote external job, the method is the same, except that:
  • You set the job's destination attribute.

  • You designate a source host for the GET_FILE procedure.

GET_FILE automatically searches the correct host location for log files for both local and remote external jobs.

Creating Detached Jobs

A detached job must point to a program object (program) that has its detached attribute set to TRUE.

Example 27-7 Creating a Detached Job That Performs a Cold Backup

This example for Linux and UNIX creates a nightly job that performs a cold backup of the database. It contains three steps.

Step 1—Create the Script That Invokes RMAN

Create a shell script that calls an RMAN script to perform a cold backup. The shell script is located in $ORACLE_HOME/scripts/coldbackup.sh. It must be executable by the user who installed Oracle Database (typically the user oracle).

#!/bin/sh
 
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 
$ORACLE_HOME/bin/rman TARGET / @$ORACLE_HOME/scripts/coldbackup.rman
  trace /u01/app/oracle/backup/coldbackup.out &
exit 0

Step 2—Create the RMAN Script

Create an RMAN script that performs the cold backup and then ends the job. The script is located in $ORACLE_HOME/scripts/coldbackup.rman.

run {
# Shut down database for backups and put into MOUNT mode
shutdown immediate
startup mount
 
# Perform full database backup
backup full format "/u01/app/oracle/backup/%d_FULL_%U" (database) ;
 
# Open database after backup
alter database open;
 
# Call notification routine to indicate job completed successfully
sql " BEGIN  DBMS_SCHEDULER.END_DETACHED_JOB_RUN(''sys.backup_job'', 0,
  null); END; ";
}

Step 3—Create the Job and Use a Detached Program

Submit the following PL/SQL block:

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name   => 'sys.backup_program',
    program_type   => 'executable',
    program_action => '?/scripts/coldbackup.sh',
    enabled        =>  TRUE);

  DBMS_SCHEDULER.SET_ATTRIBUTE('sys.backup_program', 'detached', TRUE);
 
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'sys.backup_job',
    program_name    => 'sys.backup_program',
    repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0');

  DBMS_SCHEDULER.ENABLE('sys.backup_job');
END;
/

See Also:

"Detached Jobs"

Creating Multiple Jobs in a Single Transaction

If you must create many jobs, you may be able to reduce transaction overhead and experience a performance gain if you use the CREATE_JOBS procedure. Example 27-8 demonstrates how to use this procedure to create multiple jobs in a single transaction. See Oracle Database PL/SQL Packages and Types Reference for more information.

Example 27-8 Creating Multiple Jobs in a Single Transaction

DECLARE
 newjob sys.job;
 newjobarr sys.job_array;
BEGIN
 -- Create an array of JOB object types
 newjobarr := sys.job_array();

 -- Allocate sufficient space in the array
 newjobarr.extend(5);

 -- Add definitions for 5 jobs
 FOR i IN 1..5 LOOP
   -- Create a JOB object type
   newjob := sys.job(job_name => 'TESTJOB' || to_char(i),
                     job_style => 'REGULAR',
                     job_template => 'PROG1',
                     repeat_interval => 'FREQ=HOURLY',
                     start_date => systimestamp + interval '600' second,
                     max_runs => 2,
                     auto_drop => FALSE,
                     enabled => TRUE
                    );

   -- Add it to the array
   newjobarr(i) := newjob;
 END LOOP;

 -- Call CREATE_JOBS to create jobs in one transaction
 DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
END;
/

PL/SQL procedure successfully completed.

SELECT JOB_NAME FROM USER_SCHEDULER_JOBS;
 
JOB_NAME
------------------------------
TESTJOB1
TESTJOB2
TESTJOB3
TESTJOB4
TESTJOB5
 
5 rows selected.

Altering Jobs

You alter a job using the SET_ATTRIBUTE or SET_JOB_ATTRIBUTES procedures or Enterprise Manager. All jobs can be altered, and, with the exception of the job name, all job attributes can be changed. If there is a running instance of the job when the change is made, it is not affected by the call. The change is only seen in future runs of the job.

In general, you should not alter a job that was automatically created for you by the database. Jobs that were created by the database have the column SYSTEM set to TRUE in job views. The attributes of a job are available in the *_SCHEDULER_JOBS views.

It is perfectly valid for running jobs to alter their own job attributes, however, these changes will not be picked up until the next scheduled run of the job.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE and SET_JOB_ATTRIBUTES procedures and "Configuring Oracle Scheduler".

Running Jobs

There are three ways in which a job can be run:

  • According to the job schedule—In this case, provided that the job is enabled, the job is automatically picked up by the Scheduler job coordinator and run under the control of a job slave. The job runs as the user who is the job owner. To find out whether the job succeeded, you must query the job views (*_SCHEDULER_JOBS) or the job log. See "Job Slaves" for more information job slaves and the Scheduler architecture.

  • When an event occurs—Enabled event-based jobs start when a specified event is received on an event queue. (See "Using Events".) Event-based jobs also run under the control of a job slave and run as the user who owns the job. To find out whether the job succeeded, you must query the job views or the job log.

  • By calling DBMS_SCHEDULER.RUN_JOB—You can use the RUN_JOB procedure to test a job or to run it outside of its specified schedule. You can run the job asynchronously, which is similar to the previous two methods of running a job, or synchronously, in which the job runs in the session that called RUN_JOB.

    Note:

    It is not necessary to call RUN_JOB to run a job according to its schedule. Provided that job is enabled, the Scheduler runs it automatically.

Running Jobs Asynchronously with DBMS_SCHEDULER.RUN_JOB

You run a job asynchronously with RUN_JOB by setting the use_current_session argument to FALSE. In this case, the job runs as if it were started according to its schedule or by an event. That is, it runs under the control of a job slave and runs as the job owner. The session that calls RUN_JOB returns immediately; it does not block waiting for the job to complete. To find out whether the job succeeded, you must query the job views or the job log.

Running Jobs Synchronously with DBMS_SCHEDULER.RUN_JOB

You can run a job synchronously with RUN_JOB by setting the use_current_session argument to TRUE. In this case, the job runs within the user session that invokes RUN_JOB instead of being run by a job slave. The session that calls RUN_JOB blocks until the job completes.

Running a job synchronously with RUN_JOB does not change the failure_count and run_count for the job. The job run is, however, reflected in the job log. Runtime errors generated by the job are passed back to the invoker of RUN_JOB.

When using RUN_JOB to run a remote external job or a job that points to a chain, use_current_session must be set to FALSE.

Job Run Environment

Jobs are run with the privileges that are granted to the job owner directly or indirectly through default logon roles. External operating system roles are not supported. Given sufficient privileges, users can create jobs in other users' schemas. The creator and the owner of the job can, therefore, be different. For example, if user jim has the CREATE ANY JOB privilege and creates a job in the scott schema, then the job will run with the privileges of scott.

The NLS environment of the session in which the job was created is saved and is used when the job is being executed. To alter the NLS environment in which a job runs, a job must be created in a session with different NLS settings.

Stopping Jobs

You stop one or more running jobs using the STOP_JOB procedure or Enterprise Manager. STOP_JOB accepts a comma-delimited list of jobs and job classes. If a job class is supplied, all running jobs in the job class are stopped. For example, the following statement stops job job1 and all jobs in the job class dw_jobs.

BEGIN
DBMS_SCHEDULER.STOP_JOB('job1, sys.dw_jobs');
END;
/

All instances of the designated jobs are stopped. After stopping a job, the state of a one-time job is set to STOPPED, and the state of a repeating job is set to SCHEDULED (because the next run of the job is scheduled). In addition, an entry is made in the job log with OPERATION set to 'STOPPED', and ADDITIONAL_INFO set to 'REASON="Stop job called by user: username"'.

By default, the Scheduler tries to gracefully stop a job using an interrupt mechanism. This method gives control back to the slave process, which can collect statistics of the job run. If the force option is set to TRUE, the job is abruptly terminated and certain runtime statistics might not be available for the job run.

Stopping a job that is running a chain automatically stops all running steps (by calling STOP_JOB with the force option set to TRUE on each step).

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the STOP_JOB procedure.

Caution:

When a job is stopped, only the current transaction is rolled back. This can cause data inconsistency.

Stopping External Jobs

The Scheduler offers implementors of external jobs a mechanism to gracefully clean up after their external jobs when STOP_JOB is called with force set to FALSE. The following applies only to local external jobs created without credentials on any platform, and remote external jobs on the UNIX and Linux platforms.

On UNIX and Linux, a SIGTERM signal is sent to the process launched by the Scheduler. The implementor of the external job is expected to trap the SIGTERM in an interrupt handler, clean up whatever work the job has done, and exit. On Windows, STOP_JOB with force set to FALSE is supported only on Windows XP, Windows 2003, and later operating systems. On those platforms, the process launched by the Scheduler is a console process. To stop it, the Scheduler sends a CTRL-BREAK to the process. The CTRL_BREAK can be handled by registering a handler with the SetConsoleCtrlHandler() routine.

Stopping a Chain Job

If a job pointing to a chain is stopped, all steps of the running chain that are running are stopped.

See "Stopping Individual Chain Steps" for information about stopping individual chain steps.

Dropping Jobs

You drop one or more jobs using the DROP_JOB procedure or Enterprise Manager. DROP_JOB accepts a comma-delimited list of jobs and job classes. If a job class is supplied, all jobs in the job class are dropped, although the job class itself is not dropped.

For example, the following statement drops jobs job1 and job3, and all jobs in job classes jobclass1 and jobclass2:

BEGIN
DBMS_SCHEDULER.DROP_JOB ('job1, job3, sys.jobclass1, sys.jobclass2');
END;
/

Dropping a job results in the job being removed from the job table, its metadata being removed, and it no longer being visible in the *_SCHEDULER_JOBS views. Therefore, no more runs of the job will be executed.

If an instance of the job is running at the time of the DROP_JOB call, the call results in an error. You can still drop the job by setting the force option in the call to TRUE. Setting the force option to TRUE first attempts to stop the running job instance by using an interrupt mechanism (by calling STOP_JOB with the force option set to FALSE), and then drops the job.

Alternatively, you can call STOP_JOB to first stop the job and then call DROP_JOB to drop it. If you have the MANAGE SCHEDULER privilege, you can call STOP_JOB with force, if the regular STOP_JOB call failed to stop the job, and then call DROP_JOB.

By default, force is set to FALSE.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous drop operations that were successful are committed to disk. If commit_semantics is set to TRANSACTIONAL and force is set to FALSE, then the call returns on the first error and the previous drop operations before the error are rolled back. If commit_semantics is set to ABSORB_ERRORS, then the call tries to absorb any errors and attempts to drop the rest of the jobs and commits all the drops that were successful. By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

The DROP_JOB_CLASS procedure should be used to drop a job class. See "Dropping Job Classes" for information about how to drop job classes.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DROP_JOB procedure.

Disabling Jobs

You disable one or more jobs using the DISABLE procedure or Enterprise Manager. A job can also become disabled for other reasons. For example, a job will be disabled when the job class it belongs to is dropped. A job is also disabled if either the program or the schedule that it points to is dropped. Note that if the program or schedule that the job points to is disabled, the job will not be disabled and will therefore result in an error when the Scheduler tries to run the job.

Disabling a job means that, although the metadata of the job is there, it should not run and the job coordinator will not pick up these jobs for processing. When a job is disabled, its state in the job table is changed to disabled.

When a job is disabled with the force option set to FALSE and the job is currently running, an error is returned. When force is set to TRUE, the job is disabled, but the currently running instance is allowed to finish.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous disable operations that were successful are committed to disk. If commit_semantics is set to TRANSACTIONAL and force is set to FALSE, then the call returns on the first error and the previous disable operations before the error are rolled back. If commit_semantics is set to ABSORB_ERRORS, then the call tries to absorb any errors and attempts to disable the rest of the jobs and commits all the disable operations that were successful. By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

You can also disable several jobs in one call by providing a comma-delimited list of job names or job class names to the DISABLE procedure call. For example, the following statement combines jobs with job classes:

BEGIN
DBMS_SCHEDULER.DISABLE('job1, job2, job3, sys.jobclass1, sys.jobclass2');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DISABLE procedure.

Enabling Jobs

You enable one or more jobs by using the ENABLE procedure or Enterprise Manager. The effect of using this procedure is that the job will now be picked up by the job coordinator for processing. Jobs are created disabled by default, so you need to enable them before they can run. When a job is enabled, a validity check is performed. If the check fails, the job is not enabled.

If commit_semantics is set to STOP_ON_FIRST_ERROR, then the call returns on the first error and the previous enable operations that were successful are committed to disk. If commit_semantics is set to TRANSACTIONAL, then the call returns on the first error and the previous enable operations before the error are rolled back. If commit_semantics is set to ABSORB_ERRORS, then the call tries to absorb any errors and attempts to enable the rest of the jobs and commits all the enable operations that were successful. By default, commit_semantics is set to STOP_ON_FIRST_ERROR.

You can enable several jobs in one call by providing a comma-delimited list of job names or job class names to the ENABLE procedure call. For example, the following statement combines jobs with job classes:

BEGIN
DBMS_SCHEDULER.ENABLE ('job1, job2, job3, 
   sys.jobclass1, sys.jobclass2, sys.jobclass3');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the ENABLE procedure.

Copying Jobs

You copy a job using the COPY_JOB procedure or Enterprise Manager. This call copies all the attributes of the old job to the new job (except job name). The new job is created disabled.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the COPY_JOB procedure.

Viewing the Job Log

You can view information about job runs, job state changes, and job failures in the job log. The job log is implemented as the following two data dictionary views:

  • *_SCHEDULER_JOB_LOG

  • *_SCHEDULER_JOB_RUN_DETAILS

Depending on the logging level that is in effect, the Scheduler can make job log entries whenever a job is run and when a job is created, dropped, enabled, and so on. For a job that has a repeating schedule, the Scheduler makes multiple entries in the job log—one for each job instance. Each log entry provides information about a particular run, such as the job completion status.

The following example shows job log entries for a repeating job that has a value of 4 for the max_runs attribute:

SELECT job_name, job_class, operation, status FROM USER_SCHEDULER_JOB_LOG;

JOB_NAME         JOB_CLASS            OPERATION       STATUS
---------------- -------------------- --------------- ----------
JOB1             CLASS1               RUN             SUCCEEDED
JOB1             CLASS1               RUN             SUCCEEDED
JOB1             CLASS1               RUN             SUCCEEDED
JOB1             CLASS1               RUN             SUCCEEDED
JOB1             CLASS1               COMPLETED

You can control how frequently information is written to the job log by setting the logging_level attribute of either a job or a job class. Table 27-2 shows the possible values for logging_level.

Table 27-2 Job Logging Levels

Logging Level Description

DBMS_SCHEDULER.LOGGING_OFF

No logging is performed.

DBMS_SCHEDULER.LOGGING_FAILED_RUNS

A log entry is made only if the job fails.

DBMS_SCHEDULER.LOGGING_RUNS

A log entry is made each time the job is run.

DBMS_SCHEDULER.LOGGING_FULL

A log entry is made every time the job runs and for every operation performed on a job, including create, enable/disable, update (with SET_ATTRIBUTE), stop, and drop.


Log entries for job runs are not made until after the job run completes successfully, fails, or is stopped.

The following example shows job log entries for a complete job lifecycle. In this case, the logging level for the job class is LOGGING_FULL, and the job is a non-repeating job. After the first successful run, the job is enabled again, so it runs once more. It is then stopped and dropped.

SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP, job_name,
  job_class, operation, status FROM USER_SCHEDULER_JOB_LOG
  WHERE job_name = 'JOB2' ORDER BY log_date;

TIMESTAMP            JOB_NAME  JOB_CLASS  OPERATION  STATUS
-------------------- --------- ---------- ---------- ---------
18-DEC-07 23:10:56   JOB2      CLASS1     CREATE
18-DEC-07 23:12:01   JOB2      CLASS1     UPDATE
18-DEC-07 23:12:31   JOB2      CLASS1     ENABLE
18-DEC-07 23:12:41   JOB2      CLASS1     RUN        SUCCEEDED
18-DEC-07 23:13:12   JOB2      CLASS1     ENABLE
18-DEC-07 23:13:18   JOB2                 RUN        STOPPED
18-DEC-07 23:19:36   JOB2      CLASS1     DROP

Run Details

For every row in *_SCHEDULER_JOB_LOG for which the operation is RUN, RETRY_RUN, or RECOVERY_RUN, there is a corresponding row in the *_SCHEDULER_JOB_RUN_DETAILS view. Rows from the two different views are correlated with their LOG_ID columns. You can consult the run details views to determine why a job failed or was stopped.

SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP, job_name, status,
   SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO
   FROM user_scheduler_job_run_details ORDER BY log_date;

TIMESTAMP            JOB_NAME   STATUS    ADDITIONAL_INFO
-------------------- ---------- --------- ----------------------------------------
18-DEC-07 23:12:41   JOB2       SUCCEEDED
18-DEC-07 23:12:18   JOB2       STOPPED   REASON="Stop job called by user:'SYSTEM'
19-DEC-07 14:12:20   REMOTE_16  FAILED    ORA-29273: HTTP request failed ORA-06512

The run details views also contain actual job start times and durations.

Precedence of Logging Levels in Jobs and Job Classes

Both jobs and job classes have a logging_level attribute, with possible values listed in Table 27-2. The default logging level for job classes is LOGGING_RUNS, and the default level for individual jobs is LOGGING_OFF. If the logging level of the job class is higher than that of a job in the class, then the logging level of the job class takes precedence. Thus, by default, all job runs are recorded in the job log.

For job classes that have very short and highly frequent jobs, the overhead of recording every single run might be too much and you might choose to turn the logging off or set logging to occur only when jobs fail. On the other hand, you might prefer to have a complete audit trail of everything that happens with jobs in a specific class, in which case you would enable full logging for that class.

If you want to ensure that there is an audit trail for all jobs, the individual job creator must not be able to turn logging off. The Scheduler supports this by making the class-specified level the minimum level at which job information is logged. A job creator can only enable more logging for an individual job, not less. Thus, leaving all individual job logging levels set to LOGGING_OFF ensures that all jobs in a class get logged as specified in the class.

This functionality is provided for debugging purposes. For example, if the class-specific level is set to record job runs and logging is turned off at the job level, the Scheduler still logs job runs. If, on the other hand, the job creator turns on full logging and the class-specific level is set to record runs only, the higher logging level of the job takes precedence and all operations on this individual job are logged. This way, an end user can test his job by turning on full logging.

To set the logging level of an individual job, you must use the SET_ATTRIBUTE procedure on that job. For example, to turn on full logging for a job called mytestjob, issue the following statement:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE (
   'mytestjob', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);
END;

Only a user with the MANAGE SCHEDULER privilege can set the logging level of a job class.

See Also:

"Monitoring and Managing Window and Job Logs" for more information about setting the job class logging level

Viewing stdout and stderr for External Jobs

External jobs with credentials write stdout and stderr to log files. Local external jobs write to log files in the directory ORACLE_HOME/scheduler/log. Remote external jobs write to log files in the directory AGENT_HOME/data/log. You can retrieve the contents of these files with DBMS_SCHEDULER.GET_FILE. File names consist of the string "_stdout" or "_stderr" appended to a job log ID. You obtain the job log ID for a job by querying the ADDITIONAL_INFO column of the *_SCHEDULER_JOB_RUN_DETAILS views and parsing for a name/value pair that looks similar to this:

EXTERNAL_LOG_ID="job_71035_3158"

An example file name is job_71035_3158_stdout. 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.

In addition, when a local external job or remote external job writes output to stderr, the first 200 bytes are recorded in the ADDITIONAL_INFO column of the *_SCHEDULER_JOB_RUN_DETAILS views. The information is in a name/value pair that looks like this:

STANDARD_ERROR="text"

Note:

The ADDITIONAL_INFO column can have multiple name/value pairs. The order is indeterminate, so you must parse the field to locate the STANDARD_ERROR name/value pair.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about DBMS_SCHEDULER.GET_FILE