Scheduling Jobs with Oracle Scheduler
时间:2022-03-10 17:34
In this chapter:
-
About Scheduler Objects and Their Naming
-
Creating, Running, and Managing Jobs
-
Creating and Managing Programs to Define Jobs
-
Creating and Managing Schedules to Define Jobs
-
Using Events to Start Jobs
-
Creating and Managing Job Chains
-
Prioritizing Jobs
-
Monitoring Jobs
Note:
This chapter describes how to use theDBMS_SCHEDULER
package to work with Scheduler
objects. You can accomplish the same tasks using Oracle Enterprise Manager.
See Oracle Database PL/SQL Packages and Types
Reference for DBMS_SCHEDULER
information
and the Oracle Enterprise Manager online help for information on Oracle
Scheduler pages.
About Scheduler Objects and Their Naming
You operate Oracle Scheduler by creating and managing a set of Scheduler
objects. Each Scheduler object is a complete database schema object of the
form [schema.]name
. Scheduler objects follow the naming rules
for database objects exactly and share the SQL namespace with other database
objects.
Follow SQL naming rules to name Scheduler objects in
the DBMS_SCHEDULER
package. By default, Scheduler object
names are uppercase unless they are surrounded by double quotes. For example,
when creating a job, job_name => ‘my_job‘
is the same
as job_name => ‘My_Job‘
and job_name =>
‘MY_JOB‘
, but different from job_name => ‘"my_job"‘
.
These naming rules are also followed in those cases where comma-delimited lists
of Scheduler object names are used within
the DBMS_SCHEDULER
package.
See Also:
-
Oracle Database SQL Language Reference for details regarding naming objects
-
"About Jobs and Supporting Scheduler Objects"
Creating, Running, and Managing 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:
-
Job Tasks and Their Procedures
-
Creating Jobs
-
Altering Jobs
-
Running Jobs
-
Stopping Jobs
-
Dropping Jobs
-
Disabling Jobs
-
Enabling Jobs
-
Copying Jobs
-
Viewing the Job Log
-
Viewing stdout and stderr for External Jobs
See Also:
"Jobs" for an overview of jobs. ADMIN12383Job Tasks and Their Procedures
Table 29-1 illustrates common job tasks and their appropriate procedures and privileges:
ADMIN13087Table 29-1 Job Tasks and Their Procedures
Task | Procedure | Privilege Needed |
---|---|---|
Create a job |
|
|
Alter a job |
|
|
Run a job |
|
|
Copy a job |
|
|
Drop a job |
|
|
Stop a job |
|
|
Disable a job |
|
|
Enable a job |
|
|
See "Scheduler Privileges" for further information regarding privileges.
ADMIN12384Creating Jobs
This section contains:
-
Overview of Creating Jobs
-
Specifying a Job Action and Job Schedule
-
Specifying Job Credentials and Job Destinations
-
Creating Multiple-Destination Jobs
-
Setting Job Arguments
-
Setting Additional Job Attributes
-
Creating Detached Jobs
-
Creating Multiple Jobs in a Single Transaction
-
Techniques for External Jobs
Overview of Creating Jobs
You create one or more jobs using
the DBMS_SCHEDULER.CREATE_JOB
or DBMS_SCHEDULER.CREATE_JOBS
procedures
or Enterprise Manager. You use theCREATE_JOB
procedure to
create a single job. This procedure is overloaded to enable you to create
different types of jobs that are based on different objects. You can create
multiple jobs in a single transaction using
the CREATE_JOBS
procedure.
You must have
the CREATE
JOB
privilege to create a
job in your own schema, and
the CREATE
ANY
JOB
privilege
to create a job in any schema except SYS
.
For each job being created, you specify a job type, an action, and a
schedule. You can also optionally specify a credential name, a destination or
destination group name, a job class, and other attributes. As soon as you enable
a job, it is automatically run by the Scheduler at its next scheduled date and
time. By default, jobs are disabled when created and must be enabled
with DBMS_SCHEDULER.ENABLE
to run. 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.
Some job attributes cannot be set with CREATE_JOB
, and
instead must be set with DBMS_SCHEDULER.SET_ATTRIBUTE
. For
example, to set the logging_level
attribute for a job, you must
call SET_ATTRIBUTE
after
calling CREATE_JOB
.
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. The NLS environment of the job, when it runs, is the
existing environment at the time the job was created.
Example
29-1 demonstrates creating a database job
called update_sales
, which calls a package procedure in
the OPS
schema that updates a sales summary table:
Example 29-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‘, auto_drop => FALSE, job_class => ‘batch_update_jobs‘, comments => ‘My new job‘); END; /
Because no destination_name
attribute is specified,
the job runs on the originating (local) database. The job runs as the user who
created the job.
The repeat_interval
argument specifies that this job
runs every other day until it reaches the end date and time. Another way to
limit the number of times that a repeating job runs is to set
its max_runs
attribute to a positive number.
The job is disabled when it is created, by default. You must enable it
with DBMS_SCHEDULER.ENABLE
before the Scheduler will
automatically run it.
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
).
After a job is created, it can be queried using
the *_SCHEDULER_JOBS
views.
See Also:
"Specifying Job Credentials and Job Destinations" ADMIN12502Specifying 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
29-1, 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, a schedule object (schedule) to specify the repeat interval, or 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 Named Programs and Schedules
See Also:
-
"Programs"
-
"Schedules"
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
forprogram_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. The following PL/SQL block is an example of
a CREATE_JOB
procedure with a named program that 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; /ADMIN12389
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
. The
following CREATE_JOB
procedure has a named schedule and
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; /ADMIN12390
Creating Jobs Using Named Programs and Schedules
A job can also be created by pointing to both a named program and a named
schedule. For example, the following CREATE_JOB
procedure
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; /
See Also:
-
"Creating and Managing Programs to Define Jobs"
-
"Creating and Managing Schedules to Define Jobs"
-
"Using Events to Start Jobs"
Specifying Job Credentials and Job Destinations
For local external jobs, remote external jobs, and remote database jobs, you
must specify the credentials under which the job runs. You do so by creating a
credential object and assigning it to
the credential_name
job attribute.
For remote external jobs and remote database jobs, you specify the job
destination by creating a destination object and assigning it to
thedestination_name
job attribute. A job with
a NULL
destination_name
attribute runs
on the host where the job is created.
This section contains:
-
Credential and Destination Tasks and Their Procedures
-
Creating Credentials
-
Creating Destinations
-
Creating Destination Groups for Multiple-Destination Jobs
-
Example: Creating a Remote Database Job
See Also:
-
"Credentials"
-
"Destinations"
-
"Creating Multiple-Destination Jobs"
Credential and Destination Tasks and Their Procedures
Table 29-2 illustrates credential and destination tasks and their procedures and privileges:
ADMIN13375Table 29-2 Credential and Destination Tasks and Their Procedures
Task | Procedure | Privilege Needed |
---|---|---|
Create a credential |
|
|
Drop a credential |
|
|
Create an external destination |
(none) |
See "Creating Destinations" |
Drop an external destination |
|
|
Create a database destination |
|
|
Drop a database destination |
|
|
Create a destination group |
|
|
Drop a destination group |
|
|
Add members to a destination group |
|
|
Remove members from a destination group |
|
|
Creating Credentials
A credential is a user name and password pair stored in a dedicated database object. You assign a credential to a job so that it can authenticate with an Oracle database or the operating system before running.
To create a credential:
-
Call the
DBMS_SCHEDULER.CREATE_CREDENTIAL
procedure.
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
also owns 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.
Example 29-2 Creating a Credential
BEGIN DBMS_SCHEDULER.CREATE_CREDENTIAL(‘DW_CREDENTIAL‘, ‘dwuser‘, ‘dW001515‘); END; / GRANT EXECUTE ON DW_CREDENTIAL TO salesuser;
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.
See Also:
"Credentials" for more information about credentials ADMIN13378Creating Destinations
A destination is a Scheduler object that defines a
location for running a job. You designate the locations where a job runs by
specifying either a single destination or a destination group in
the destination_name
attribute of the job. If you leave
the destination_name
attribute NULL
,
the job runs on the local host (the host where the job was created).
Use external destinations to specify locations where remote external jobs run. Use database destinations to specify locations where remote database jobs run.
You do not need object privileges to use a destination created by another user.
To create an external destination:
-
Register a remote Scheduler agent with the database.
See "Installing and Configuring the Scheduler Agent on a Remote Host" for instructions.
Note:
There is noDBMS_SCHEDULER
package procedure to create an external
destination. You create an external destination implicitly by registering a
remote agent.
You can also register a local Scheduler agent if you have other database instances on the same host that are targets for remote jobs. This creates an external destination that references the local host.
The external destination name is automatically set to the agent name. To
verify that the external destination was created, query the
viewsDBA_SCHEDULER_EXTERNAL_DESTS
or ALL_SCHEDULER_EXTERNAL_DESTS
.
To create a database destination:
-
Call the
DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION
procedure.You must specify the name of an external destination as a procedure argument. This designates the remote host that the database destination points to. You also specify a net service name or complete connect descriptor that identifies the database instance being connected to. If you specify a net service name, it must be resolved by the local
tnsnames.ora
file. If you do not specify a database instance, the remote Scheduler agent connects to its default database, which is specified in the agent configuration file.
To create a database destination, you must have the CREATE
JOB
system privilege. To create a database destination in a schema
other than your own, you must have the CREATE ANY
JOB
privilege.
Example 29-3 Creating a Database Destination
The following example creates a database destination
named DBHOST1_ORCLDW
. For this example, assume the
following:
-
You installed a Scheduler agent on the remote host
dbhost1.example.com
, and you registered the agent with the local database. -
You did not modify the agent configuration file to set the agent name. Therefore the agent name and the external destination name default to
DBHOST1
. -
You used Net Configuration Assistant on the local host to create a connect descriptor in tnsnames.ora for the Oracle Database instance named
orcldw
, which resides on the remote hostdbhost1.example.com
. You assigned a net service name (alias) ofORCLDW
to this connect descriptor.
BEGIN DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION ( destination_name => ‘DBHOST1_ORCLDW‘, agent => ‘DBHOST1‘, tns_name => ‘ORCLDW‘, comments => ‘Instance named orcldw on host dbhost1.example.com‘); END; /
To verify that the database destination was created, query the
views *_SCHEDULER_DB_DESTS
.
See Also:
-
"Destinations" for more information about destinations
-
"Jobs" to learn about remote external jobs and remote database jobs
Creating Destination Groups for Multiple-Destination Jobs
To create a job that runs on multiple destinations, you must create a
destination group and assign that group to
the destination_name
attribute of the job. You can
specify group members (destinations) when you create the group, or you can add
group members at a later time.
To create a destination group:
-
Call the
DBMS_SCHEDULER.CREATE_GROUP
procedure.
For remote external jobs you must specify a group of type
‘EXTERNAL_DEST
‘, and all group members must be external
destinations. For remote database jobs, you must specify a group of type
‘DB_DEST
‘, and all members must be database destinations.
Members of destination groups have the following format:
[[schema.]credential@][schema.]destination
where:
-
credential
is the name of an existing credential. -
destination
is the name of an existing database destination or external destination
The credential portion of a destination member is optional. If omitted, the job using this destination member uses its default credential.
You can include another group of the same type as a member of a destination group. Upon group creation, the Scheduler expands the included group into its members.
If you want the local host to be one of many destinations on which a job
runs, you can include the keyword LOCAL
as a group member
for either type of destination group. LOCAL
can be
preceded by a credential only in an external destination group.
A group is owned by the user who creates it. You must have
the CREATE
JOB
system privilege to
create a group in your own schema, and
the CREATE
ANY
JOB
system
privilege to create a group in another schema. You can grant object privileges
on a group to other users by granting SELECT
on the
group.
Example 29-4 Creating a Database Destination Group
This example creates a database destination group. Because some members do not include a credential, a job using this destination group must have default credentials.
BEGIN DBMS_SCHEDULER.CREATE_GROUP( GROUP_NAME => ‘all_dbs‘, GROUP_TYPE => ‘DB_DEST‘, MEMBER => ‘oltp_admin@orcl, orcldw1, LOCAL‘, COMMENTS => ‘All databases managed by me‘); END; /
The following code adds another member to the group.
BEGIN DBMS_SCHEDULER.ADD_GROUP_MEMBER( GROUP_NAME => ‘all_dbs‘, MEMBER => ‘dw_admin@orcldw2‘); END; /
See Also:
"Groups" for an overview of groups. ADMIN13382Example: Creating a Remote Database Job
The following example creates a remote database job by specifying a database
destination object in the destination_name
object of the
job. A credential must also be specified so the job can authenticate with the
remote database. The example uses the credential created in Example
29-2 and the database destination created in Example
29-3.
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => ‘SALES_SUMMARY1‘, job_type => ‘STORED_PROCEDURE‘, job_action => ‘SALES.SALES_REPORT1‘, start_date => ‘15-JUL-09 11.00.00 PM Europe/Warsaw‘, repeat_interval => ‘FREQ=DAILY‘, credential_name => ‘DW_CREDENTIAL‘, destination_name => ‘DBHOST1_ORCLDW‘); END; /ADMIN13383
Creating Multiple-Destination Jobs
You can create a job that runs on multiple destinations, but that is managed from a single location. A typical reason to do this is to run a database maintenance job on all of the databases that you administer. Rather than create the job on each database, you create the job once and designate multiple destinations for the job. From the database where you created the job (the local database), you can monitor the state and results of all instances of the job at all locations.
To create a multiple-destination job:
-
Call the
DBMS_SCHEDULER.CREATE_JOB
procedure and set thedestination_name
attribute of the job to the name of database destination group or external destination group.If not all destination group members include a credential prefix (the schema), assign a default credential to the job.
To include the local host or local database as one of the destinations on which the job runs, ensure that the keyword
LOCAL
is one of the members of the destination group.
To obtain a list of destination groups, submit this query:
SELECT owner, group_name, group_type, number_of_members FROM all_scheduler_groups WHERE group_type = ‘DB_DEST‘ or group_type = ‘EXTERNAL_DEST‘; OWNER GROUP_NAME GROUP_TYPE NUMBER_OF_MEMBERS --------------- --------------- ------------- ----------------- DBA1 ALL_DBS DB_DEST 4 DBA1 ALL_HOSTS EXTERNAL_DEST 4
The following example creates a multiple-destination database job, using the database destination group created in Example 29-4. Because this is a system administration job, it uses a credential with system administrator privileges.
BEGIN DBMS_SCHEDULER.CREATE_CREDENTIAL(‘DBA_CREDENTIAL‘, ‘dba1‘, ‘sYs040533‘); DBMS_SCHEDULER.CREATE_JOB ( job_name => ‘MAINT_SET1‘, job_type => ‘STORED_PROCEDURE‘, job_action => ‘MAINT_PROC1‘, start_date => ‘15-JUL-09 11.00.00 PM Europe/Warsaw‘, repeat_interval => ‘FREQ=DAILY‘, credential_name => ‘DBA_CREDENTIAL‘, destination_name => ‘ALL_DBS‘); END; /
See Also:
-
"Multiple-Destination Jobs"
-
"Monitoring Multiple Destination Jobs"
-
"Groups"
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.
SET_JOB_ARGUMENT_VALUE
only supports arguments of SQL type.
Therefore, argument values that are not of SQL type, such as booleans, are not
supported as program or job arguments.
See Also:
"Defining Program Arguments" ADMIN12385Setting Additional Job Attributes
After creating a job, you can set additional job attributes or change
attribute values by using
the SET_ATTRIBUTE
or SET_JOB_ATTRIBUTES
procedures.
You can also set job attributes with Enterprise Manager. Although many job
attributes can be set with the call to CREATE_JOB
, some
attributes, such
asdestination
and credential_name
, can be
set only
with SET_ATTRIBUTE
or SET_JOB_ATTRIBUTES
after
the job is created.
Creating Detached Jobs
A detached job must point to a program object (program) that has
its detached
attribute set to TRUE
.
Example 29-5 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.2.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" ADMIN12516Creating 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
29-6 demonstrates how to use this procedure to create multiple jobs in
a single transaction.
Example 29-6 Creating Multiple Jobs in a Single Transaction
DECLARE newjob sys.job_definition; newjobarr sys.job_definition_array; BEGIN -- Create an array of JOB_DEFINITION object types newjobarr := sys.job_definition_array(); -- Allocate sufficient space in the array newjobarr.extend(5); -- Add definitions for 5 jobs FOR i IN 1..5 LOOP -- Create a JOB_DEFINITION object type newjob := sys.job_definition(job_name => ‘TESTJOB‘ || to_char(i), job_style => ‘REGULAR‘, program_name => ‘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.
See Also:
"Lightweight Jobs" ADMIN13384Techniques for External Jobs
This section contains the following examples, which demonstrate some practical techniques for external jobs:
-
Creating a Local External Job That Runs a DOS Command
-
Creating a Local External Job and Retrieving stdout
Example 29-7 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.
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, credential_name => ‘TESTCRED‘); 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.ENABLE(‘MKDIR_JOB‘); END; /ADMIN13093
Example 29-8 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/password 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, credential_name => ‘my_cred‘); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(‘lsdir‘,1,‘/tmp‘); 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_blob blob; 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_blob, false); dbms_scheduler.get_file( source_file => log_id ||‘_stdout‘, credential_name => ‘my_cred‘, file_contents => my_blob, source_host => null); dbms_output.put_line(my_blob); end; /
Note:
For a remote external job, the method is the same, except that:-
You set the job‘s
destination_name
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.
See Also:
-
Oracle Database Security Guide for more information about external authentication
-
"External Jobs"
-
"Viewing stdout and stderr for External Jobs"
-
"Stopping External Jobs"
-
"Troubleshooting Remote Jobs"
Altering Jobs
You alter a job by modifying its attributes. You do so using
the SET_ATTRIBUTE
, SET_ATTRIBUTE_NULL
,
or SET_JOB_ATTRIBUTES
package procedures or Enterprise
Manager. See the CREATE_JOB
procedure in Oracle Database PL/SQL Packages and Types
Reference for details on job attributes.
All jobs can be altered, and, except for 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
columnSYSTEM
set to TRUE
in job views.
The attributes of a job are available in
the *_SCHEDULER_JOBS
views.
It is valid for running jobs to alter their own job attributes. However, these changes do not take effect until the next scheduled run of the job.
See Oracle Database PL/SQL Packages and Types
Reference for detailed information about
the SET_ATTRIBUTE
, SET_ATTRIBUTE_NULL
,
andSET_JOB_ATTRIBUTES
procedures.
The following example changes the repeat_interval
of
the job update_sales
to once per week on Wednesday.
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => ‘update_sales‘, attribute => ‘repeat_interval‘, value => ‘freq=weekly; byday=wed‘); END; /ADMIN12393
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, or in the case of a local external job with a credential, as the user named in the credential. To find out whether the job succeeded, you must query the job views (
*_SCHEDULER_JOBS
) or the job log (*_SCHEDULER_JOB_LOG
and*_SCHEDULER_JOB_RUN_DETAILS
). See "How Jobs Execute" 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 or when a file watcher raises a file arrival event. (See "Using Events to Start Jobs".) Event-based jobs also run under the control of a job slave and run as the user who owns the job, or in the case of a local external job with a credential, as the user named in the credential. 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 theRUN_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 calledRUN_JOB
, and as the user logged in to that session. Theuse_current_session
argument ofRUN_JOB
determines whether a job runs synchronously or asynchronously.RUN_JOB
accepts a comma-delimited list of job names.The following example asynchronously runs two jobs:
BEGIN DBMS_SCHEDULER.RUN_JOB( JOB_NAME => ‘DSS.ETLJOB1, DSS.ETLJOB2‘, USE_CURRENT_SESSION => FALSE); END; /
Note:
It is not necessary to callRUN_JOB
to run a job according to its schedule. Provided that job is enabled, the Scheduler runs it automatically.
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,
job classes, and job destination IDs. A job destination ID is a number, assigned by the
Scheduler, that represents a unique combination of a job, a credential, and a
destination. It serves as a convenient method for identifying a particular child
job of a multiple-destination job and for stopping just that child. You obtain
the job destination ID for a child job from
the *_SCHEDULER_JOB_DESTS
views.
If a job class is supplied, all running jobs in the job class are stopped.
For example, the following statement stops job job1
, all jobs
in the job classdw_jobs
, and two child jobs of a
multiple-destination job:
BEGIN DBMS_SCHEDULER.STOP_JOB(‘job1, sys.dw_jobs, 984, 1223‘); 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
‘,
andADDITIONAL_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
run-time 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).
You can use the commit_semantics
argument
of STOP_JOB
to control the outcome if multiple jobs are
specified and errors occur when trying to stop one or more jobs. If you set this
argument to ABSORB_ERRORS
, the procedure may be able to
continue after encountering an error and attempt to stop the remaining jobs. If
the procedure indicates that errors occurred, you can query the
view SCHEDULER_BATCH_ERRORS
to determine the nature of
the errors. See "Dropping
Jobs" for a more detailed discussion of commit semantics.
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. ADMIN12400Stopping 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 theSIGTERM
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.
ADMIN12401Dropping 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.
(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.) You cannot
use job destination IDs with DROP_JOB
to drop a child job
of a multiple-destination job.
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; /
If a job is running at the time of the procedure call, the attempt to drop
the job fails. You can modify this default behavior by setting either
the force
ordefer
option.
When you set the force
option
to TRUE
, the Scheduler first attempts to stop the running job
by using an interrupt mechanism—calling STOP_JOB
with
theforce
option set to FALSE
. If the job is
successfully stopped, the job is then dropped. Alternatively, you can
call STOP_JOB
to first stop the job and then
callDROP_JOB
. If STOP_JOB
fails, you can
call STOP_JOB
with
the force
option, provided you have the MANAGE
SCHEDULER
privilege. You can then drop the job. By
default, force
is set to FALSE
for
both
the STOP_JOB
and DROP_JOB
procedures.
When you set the defer
option
to TRUE
, the running job is allowed to complete and is then
dropped.
The force
and defer
options are
mutually exclusive; setting both results in an error.
When you specify multiple jobs to drop,
the commit_semantics
argument determines the outcome when
an error occurs on one of the jobs. The following are the possible values for
this argument:
-
STOP_ON_FIRST_ERROR
, the default—The call returns on the first error and the previous drop operations that were successful are committed to disk. -
TRANSACTIONAL
—The call returns on the first error and the previous drop operations before the error are rolled back.force
must beFALSE
. -
ABSORB_ERRORS
—The call tries to absorb any errors, attempts to drop the rest of the jobs, and commits all the drops that were successful.
Setting commit_semantics
is valid only when no job
classes are included in the job_name
list. When you
include job classes, default commit semantics (STOP_ON_FIRST_ERROR
)
are in effect.
The following example drops the
jobs myjob1
and myjob2
with
the defer
option and with transactional commit
semantics:
BEGIN DBMS_SCHEDULER.DROP_JOB( job_name => ‘myjob1, myjob2‘, defer => TRUE, commit_semantics => ‘TRANSACTIONAL‘); END; /
This next example illustrates the ABSORB_ERRORS
commit
semantics. Assume that myjob1
is running when the
procedure is called and that myjob2
is not.
BEGIN DBMS_SCHEDULER.DROP_JOB( job_name => ‘myjob1, myjob2‘, commit_semantics => ‘ABSORB_ERRORS‘); END; / Error report: ORA-27362: batch API call completed with errors
You can query the view SCHEDULER_BATCH_ERRORS
to determine the
nature of the errors.
SELECT object_name, error_code, error_message FROM scheduler_batch_errors; OBJECT_NAME ERROR code ERROR_MESSAGE -------------- ---------- --------------------------------------------------- STEVE.MYJOB1 27478 "ORA-27478: job "STEVE.MYJOB1" is running
Checking USER_SCHEDULER_JOBS
, you would find
that myjob2
was successfully dropped and
that myjob1
is still present.
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. If the procedure indicates that errors occurred, you can
query the viewSCHEDULER_BATCH_ERRORS
to determine the nature
of the errors.
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 must
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 you enable a disabled job, it begins to run immediately according to its
schedule. Enabling a disabled job also resets the
job RUN_COUNT
, FAILURE_COUNT
,
and RETRY_COUNT
attributes.
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. If the procedure indicates that errors occurred, you can
query the view SCHEDULER_BATCH_ERRORS
to determine the nature
of the errors.
By default, commit_semantics
is set
to STOP_ON_FIRST_ERROR
.