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