Oracle 10.2 Database Administrator Guide-Chapter 27

27 Using the Scheduler

Oracle database provides database job capabilities through Oracle Scheduler (the Scheduler). This chapter explains the various Scheduler, and discusses the following topics:

Dispatcher objects and their naming

Using jobs

Using programs

Using schedules

Using Job Classes

Using Windows

Using Window Groups

Use events

Using chains

Allocating resources through jobs

Note:

This chapter describes "how to" use the "Dbms_scheduler package" with SCHEDULER components. You can accomplish the same tasks using Oracle Enterprise Manager.

Using jobs

A job is a combination of a schedule and a program, as well as additional parameters that are required by the program. This section introduces you to the basic job tasks and discusses the following topics:

Job tasks and their stored procedures

Create jobs

Copy jobs

Modify Jobs

Run jobs

Stop jobs

Still losing jobs.

Close jobs

Open jobs

See Also:

"Jobs" for an overview of jobs.

Create jobs

You can create jobs using create_job stored procedures or EM. When creating a job, you can specify an action, a schedule, and other properties. For example, the following statement creates a job called Update_sales, which calls a stored stored procedure to update a Sales Overview table in OPS mode:

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-03 07.00.00 PM Australia/sydney ',

Repeat_interval = ' freq=daily;interval=2 ',/* every other day * *

end_date = ' 20-nov-04 07.00.00 PM Australia/sydney ',

Job_class = ' Batch_update_jobs ',

Comments = ' My new job ');

END;

/

You can create a job with another schema by specifying schema.job_name. Therefore, the creator of a job is not necessarily the owner of the job. The owner of the job is the user who is creating the job when the job is created for that user, the job creator. The job is created with permission to use the schema created by the job. The NLS environment variable at the job runtime is the NLS environment variable that is rendered when the job is created.

After a job was created, it can be queried using the ' views ' *_SCHEDULER_JOBS . Jobs is created disabled by default and need to is enabled to run.

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

Ways of Creating Jobs

You create a job using the Create_job procedure or Enterprise Manager. Because This procedure was overloaded, there is several different ways of using it. In addition to inlining a job during the job creation, you can also create a job that points to a named program and schedu Le. This was 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

Creating Jobs Using a Named program

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

When creating a job is existing, the owner of the job must is the owner of the program or has EXECUTE Privil Eges on it. An example CREATE_JOB of using the procedure with a named program is the following statement, which creates a job called my_new_job1 :

BEGIN

Dbms_scheduler. Create_job (

job_name = ' My_new_job1 ',

Repeat_interval = ' freq=daily; Byhour=12 ',

Comments = ' Daily at noon ');

END;

/

Creating Jobs Using a Named Schedule

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

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

BEGIN

Dbms_scheduler. Create_job (

Job_type = ' Plsql_block ',

job_action = ' BEGIN sales_pkg. Update_sales_summary; END; ',

Schedule_name = ' my_saved_schedule ');

END;

/

Creating Jobs Using a Named program and Schedule

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

BEGIN

Dbms_scheduler. Create_job (

Schedule_name = ' my_saved_schedule1 ');

END;

/

Copying Jobs

You copy a job using the COPY_JOB procedure or Enterprise Manager. The copies all the attributes of the new job except the new job was created disabled and has another n Ame.

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

Altering Jobs

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

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

It is perfectly valid for running jobs to alter their own job attributes using SET_ATTRIBUTE The procedure, however, these changes Won't is picked up until the next scheduled run of the job.

See Oracle Database PL/SQL Packages and Types Reference For detailed information about SET_ATTRIBUTE the procedure and "Configurin G The Scheduler ".

Oracle 10.2 Database Administrator Guide-Chapter 27