Scheduler based job in Oracle

Based on the setup of the scheduler job, the basic creation script:

Sys.dbms_scheduler.create_job (

Job_name => ' "SYS". " Rebuild_job1 "',

Program_name => ' "SYS". " Emp_ind_rebuild "',

Schedule_name => ' "SYS". " Dailyrebuild "',

Job_class => ' "Default_job_class",

Comments => ' rebuild ',

Auto_drop => TRUE,

Enabled => ture);

Name of the user and job of Job_name:job

Program_name: The name of the program called by this job

Schedule_name: The name of the scheduler used by this job

Job_class: The name of the job class where the job is located

Note: Job class is equivalent to creating a job group where DBAs can place jobs with the same characteristics in the same job classes, and then apply the Resource Usage plan attribute in Oracle to the job class Manage the resource allocation required during the execution of the process.

Comments: Description of this Job

Auto_drop: Whether to drop automatically after job is completed

Enabled: Is it effective

There are also some parameters that cannot be set when a job is created, as described below:

Some event that the raise_events:job triggers will not trigger an event by default, but can be modified after the job is created.

L Job_started:job start;

L Job_succeeded:job successful end;

l Job_failed:job execution failure;

L Job_broken:job is placed in the broken state;

L JOB_COMPLETED:JOB The maximum number of runs, or the end date of the operation;

L Job_stopped:job is placed by the stop_job process as a state to stop execution;

L The schedule of Job_sch_lim_reached:job reaches the limited value;

L Job_disabled:job is placed in disable state;

L Job_chain_stalled: The job that runs in chain is placed in chain_stalled state;

L Job_all_events: Including all types mentioned above;

L job_run_completed: The job ran out of error, ended successfully, or was manually stopped.

The way it is modified is as follows:

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Rebuild_job1 "', attribute => ' raise_events ', value => dbms_scheduler.job_started + dbms_scheduler.job_succeeded + dbms_scheduler.job_failed + dbms_scheduler.job_broken + dbms_scheduler.job_completed + dbms_scheduler.job_stopped + dbms_scheduler.job_sch_lim_reached + dbms_scheduler.job_disabled + dbms_scheduler.job_chain_stalled);

Restartable: Automatic re-execution when job execution fails.

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Rebuild_job1 "', attribute => ' restartable ', value => TRUE);

Schedule_limit: Allow the maximum latency of the job, if the system load is heavy, when the schedule set to run time to get the appropriate resources to perform the job, this time if the schedule is set to NULL, The job will be executed after the resource is acquired, no matter how long it waits to be executed. If the Schedule_limit setting is set, the job will skip this execution if the setting value is exceeded. This execution will not be defined as execution failure and will react in the job log. Its value range from 1 minutes to 99 days

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Rebuild_job1 "', attribute => ' Schedule_limit ', Value => numtodsinterval (" Minute "));

Max_run_duration: Sets a valid time for the job to run, and if a value is set, the dispatch reports the Job_over_max_dur event when the value is reached, and then the event handler decides whether to proceed.

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Rebuild_job1 "', attribute => ' max_run_duration ', Value => numtodsinterval (ten, ' minute '));

The maximum number of times the max_runs:job executes, ranging from 1 to 1000000, and the default is null (meaning that the job repeats, either to the end_date of the job execution, or to the number of specified failures). Once the maximum set is reached, the job will be disable and the status changed to completed.

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Rebuild_job1 "', attribute => ' max_runs ', value => 10);

Instance_stickiness: for RAC databases only. The default is true, set to true, the job runs on the lightest node, or if a node is closed or the load is too heavy, the new job is not started, but there is another node to execute the job.

Job_priority: Priority at the same class,job execution, defaults to 3. range from 1 to 5.

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Rebuild_job1 "', attribute => ' job_priority ', Value => 2);

Max_failures: The number of times the job failed to be allowed, ranging from 1 to 1000000, and null by default.

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Rebuild_job1 "', attribute => ' max_failures ', value => 5);

Job_weight: If you use parallel execution techniques, this parameter sets the degree of parallelism in the job to execute SQL.

Sys.dbms_scheduler.set_attribute (name => ' SYS ".) Rebuild_job1 "', attribute => ' job_weight ', value => 5);

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/