| Oracle® Database Administrator's Guide 11g Release 2 (11.2) Part Number E25494-02 |
|
|
PDF · Mobi · ePub |
| Oracle® Database Administrator's Guide 11g Release 2 (11.2) Part Number E25494-02 |
|
|
PDF · Mobi · ePub |
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.Table 29-1 illustrates common job tasks and their appropriate procedures and privileges:
Table 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.
This section contains:
You create one or more jobs using the DBMS_SCHEDULER.CREATE_JOB or DBMS_SCHEDULER.CREATE_JOBS procedures or Enterprise Manager. You use the CREATE_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:
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.
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:
See Also:
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. 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; /
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; /
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; /
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 the destination_name job attribute. A job with a NULL destination_name attribute runs on the host where the job is created.
This section contains:
Table 29-2 illustrates credential and destination tasks and their procedures and privileges:
Table 29-2 Credential and Destination Tasks and Their Procedures
| Task | Procedure | Privilege Needed |
|---|---|---|
|
Create a credential |
|
|
|
Drop a credential |
|
|
|
Create an external destination |
(none) |
|
|
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 |
|
|
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 credentialsA 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 views DBA_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 host dbhost1.example.com. You assigned a net service name (alias) of ORCLDW 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
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.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; /
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 the destination_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;
/
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"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 as destination and credential_name, can be set only with SET_ATTRIBUTE or SET_JOB_ATTRIBUTES after the job is created.
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"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"This section contains the following examples, which demonstrate some practical techniques for external jobs:
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;
/
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_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_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
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 column SYSTEM 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, and SET_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; /
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 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, and as the user logged in to that session. The use_current_session argument of RUN_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.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 class dw_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', 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 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.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.
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.
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 or defer 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 the force 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 call DROP_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 be FALSE.
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.
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 view SCHEDULER_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.
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.
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.
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.
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 29-8, "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:
TheADDITIONAL_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 aboutDBMS_SCHEDULER.GET_FILE
|
 Copyright © 2001, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|