Oracle scheduler job (DBMS_Scheduler)

Organize SchedulerThis part is due to the fact that the execution time of automatic statistics collection on the system is abnormal recently, and the execution time is defined in the morning (this is not a reasonable and reliable time ). this item was also sorted out while the configuration was re-modified.

First, let's briefly talk about Oracle 10g schedjob and 10g introduce dbms_schedjob to replace the previous dbms_job. In terms of functions, it provides more powerful functions and more flexible mechanisms/management than dbms_job. it consists of the following parts:

1. Job):

The entity of a scheduler job. it can be created and generated by dbms_scheduler.create_job. it can specify the job attributes by itself, or call a series of scheduler/Program/chain/job_class/window/window_group that we created in advance to match the job attributes.

2. sched):

The time policy for executing a task plan. for example, if we want to create a Task Plan to be executed at three o'clock in the evening, we can create a scheduling. any task that meets the scheduling requirements can call the pre-created scheduling. you can use dbms_scheduler.create_schedule to create a scheduling task.

For example, I create a schedule named mytest_schedule, which is executed at every day.

Begin
Dbms_scheduler.create_schedule (
Repeat_interval => 'freq = daily; byhour = 4; byminute = 0; bysecond = 0 ',
Start_date => systimestamp at Time Zone 'prc ',
Comments => '--- this is my test schedule ---',
Schedule_name => 'mytest _ schedule ');
End;

3. Program (Program):

You can create a program using dbms_scheduler.create_program in PL/SQL block/stored procedure/executable.

Begin
Dbms_scheduler.create_program (
Program_name => 'mytest _ program_1 ',
Program_action => 'Update mytest Set ID = ID + 1 ;',
Program_type => 'plsql _ Block ',
Number_of_arguments => 0,
Comments => '',
Enabled => true );
End;

4. Chain):

A chain can be seen as a collection of several Program/event scheduler. To maintain the need, we may need to put many different programs together for execution in sequence, according to the previous mode, either the several programs can be integrated into a large whole, or separate several jobs for separate execution, which undoubtedly increases the maintenance burden, and the emergence of chain can optimize this problem, we integrate the defined program and create a job for execution. You can use dbms_scheduler.create_chain to create a chain.

For example, in my system, I created an executable type and a stored procedure type program respectively. I need them to execute them in sequence, so I can do this:

Begin
Dbms_scheduler.create_chain (
Chain_name => 'mytest _ chain ');
Dbms_scheduler.define_chain_step (
Chain_name => 'mytest _ chain ',
Step_name => 'mytest _ chain_1 ',
Program_name => 'P _ 1 ');
Dbms_scheduler.alter_chain (
Chain_name => 'mytest _ chain ',
Step_name => 'mytest _ chain_1 ',
Attribute=> 'Skip ',
Value=>False);
Dbms_scheduler.define_chain_step (
Chain_name => 'mytest _ chain ',
Step_name => 'mytest _ chain_2 ',
Program_name => 'P _ 2 ');
Dbms_scheduler.alter_chain (
Chain_name => 'mytest _ chain ',
Step_name => 'mytest _ chain_2 ',
Attribute=> 'Skip ',
Value=>False);
DBMS_Scheduler.Enable('Mytest _ chain ');
End;

4. Job class (job_class ):

Defines the resource user group for running jobs. By using the resource plan in the window, we can allocate resources between different resource groups and different job classes. You can use dbms_scheduler.create_job_class to create a job class.

Begin
Dbms_scheduler.create_job_class (
Logging_level => dbms_scheduler.logging_runs,
Log_history => 100,
Resource_consumer_group => 'Auto _ task_consumer_group ',
Job_class_name => 'mytest _ job_class ');
End;

5. window ):

It can be viewed as a higher-function scheduling. The window can call the scheduling existing in the system (or you can customize the execution time), and has the resource plan restriction function, A window can belong to a window group.

You can use dbms_scheduler.create_window to create a window.

For example, I created a window named mytest_windows_1 and used the scheduling method of daily_purge_schedule. The resource plan restriction scheme is system_plan and the duration is 4 hours.

Begin
Dbms_scheduler.create_window (
Window_name => 'mytest _ windows_1 ',
Resource_plan => 'System _ plan ',
Schedule_name => 'sys. daily_purge_schedule ',
Duration => numtodsinterval (240, 'minute '),
Window_priority => 'low ',
Comments => '');
End;

6. Window group (window_group ):

A collection of one or more windows. by default, the scheduling of automatic statistics collection for 10 Gb is in the form of a window group. For example, if two windows are set, the task is executed at on Friday and on the window, window 2 is set to run at a.m. on Saturday. These two windows form a window group to form the execution scheduling policy for the job.

You can use dbms_scheduler.create_window_group to create a window group.

Begin
Dbms_scheduler.create_window_group (
Group_name => 'mytest _ window_group ',
Window_list => 'mytest _ windows_1, weekend_window ');
End;

Therefore, you can use DBMS_Scheduler to create a job. You can call various predefined complex attributes, or manually Specify required attributes.

Simple, such

Begin
Dbms_scheduler.create_job (
Job_name => 'mytest_job_1 ',
Job_type => 'plsql _ Block ',
Job_action => 'Update mytest Set ID = ID + 1 ;',
Repeat_interval => 'freq = minutely; interval = 10 ',
Start_date => sysdate,
Auto_drop => false,
Enabled => true );
End;

It is not much different from dbms_job, but there is a difference in syntax such as repeat_interval.

Complex, such:

Begin
Dbms_scheduler.create_job (
Job_name => 'mytest _ job2 ',
Job_type => 'chain ',
Job_action => 'mytest _ chain ',
Schedule_name => 'sys. mytest_window_group ',
Job_class => 'default _ job_class ',
Auto_drop => false,
Enabled => false );
Dbms_scheduler.set_attribute (name => 'mytest _ job2', attribute => 'Stop _ on_window_close ', value => false );
End;

Modify the attributes of a scheduler job:

You can use dbms_scheduler.set_attribute and dbms_scheduler.set_attribut_null to modify attributes of most scheduling jobs. Here are only a few examples to illustrate how to use these attributes.

^ For example, redefine the scheduler attribute:

Begin
Dbms_scheduler.set_attribute (name => 'daily _ purge_schedule ', attribute => 'Repeat _ interval', value => 'freq = daily; byhour = 1; byminute = 0; bysecond = 0 ');
End;

^ For example, re-modify the program;

Begin
Dbms_scheduler.set_attribute (
Name => 'P _ 1 ',
Attribute => 'program _ action ',
Value => '/backup/2. Sh ');
End;

^ For example, redefining a job attribute

Begin
Dbms_scheduler.set_attribute_null (name => 'Collect _ stats_job ', attribute => 'schedule _ name ');

Dbms_scheduler.set_attribute (name => 'gather _ stats_job ', attribute => 'Repeat _ interval', value => 'freq = daily; byhour = 2; bysecond = 0 ');
End;

Wait .......

Related tables/View:

SQL> select view_name from dba_views A where a. view_name like 'dba _ scheduler % ';

View_name

------------------------------

Dba_scheduler_programs

Dba_scheduler_jobs

Dba_scheduler_job_classes

Dba_scheduler_windows

Dba_scheduler_program_args

Dba_scheduler_job_args

Dba_scheduler_job_log

Dba_scheduler_job_run_details

Dba_scheduler_window_log

Dba_scheduler_window_details

Dba_scheduler_window_groups

Dba_scheduler_wingroup_members

Dba_scheduler_schedules

Dba_scheduler_running_jobs

Dba_scheduler_global_attribute

Dba_scheduler_chains

Dba_scheduler_chain_rules

Dba_scheduler_chain_steps

Dba_scheduler_running_chains

It should be said that these views are literally easy to understand and do not describe too much.