Using Jobs

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:

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

• Creating Jobs Using a Named Schedule

• Creating Jobs Using a Named Program and Schedule

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;
/

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;
/

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;
/

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.

   • For local external jobs, consult your Oracle Database platform guide.

   • For remote external jobs, complete the database setup procedure described in "Setting Up the Database for Remote 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.

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;
/

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;
/

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;
/

#!/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

-- 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;
/

Creating Detached Jobs

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

#!/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;
/

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.

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.

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 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.

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.