[Oracle] SCHEDULER-based job details

Yesterday I explained how to set scheduler parameters. Today I want to explain how to set scheduler jobs. First, let's take a look at 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 => 'rebuilt ',

Auto_drop => TRUE,

Enabled => TURE );

 

Job_name: user of the job and name of the job

Program_name: name of the program called by this job

Schedule_name: name of the scheduler used by this job

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

Note: Job Class is equivalent to creating a job group. DBA can put all jobs with the same features into the same job Classes, then, by applying the "resource usage plan" feature in ORACLE to the Job Class, you can manage the resource allocation required during the execution of these jobs.

Comments: Description of the job

Auto_drop: whether to automatically drop the job after the job is completed

Enabled: whether it takes effect

 

Some parameters cannot be set when a job is created. The following describes the parameters:

Raise_events: Some events that a job will trigger. events are not triggered by default, but can be modified after the job is created.

L job_started: Start a JOB;

L job_succeeded: the JOB is successfully completed;

L job_failed: JOB execution failed;

L job_broken: the JOB is set to BROKEN;

L job_completed: the maximum number of jobs or the end date of the JOB;

L job_stopped: the JOB is set to stop execution by the STOP_JOB process;

L job_sch_lim_reached: the schedule of the Job reaches the limit;

L job_disabled: the JOB is placed in the DISABLE state;

L job_chain_stalled: the JOB running on the chain is placed in the CHAIN_STALLED state;

L job_all_events: contains all the types mentioned above;

L job_run_completed: due to a Job running error, successful completion, or manual stop.

The modification method is as follows:

Sys. dbms_scheduler.set_attribute (name => '"SYS ". "REBUILD_JOB1" ', attribute => 'raise _ events', value => combine + merge + dbms_scheduler.job_completed + merge + dbms_scheduler.job_disabled + Enabled );

 

 

Restartable: whether to perform automatic re-execution when the job fails to be executed.

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

 

 

Schedule_limit: maximum delay time allowed for a job. If the system load is heavy, the corresponding resources cannot be obtained to execute the job when the run time set by schedule is reached, at this time, if schedule is set to null, the job will be executed after the resource is obtained, no matter how long it will wait for execution. If the value of schedule_limit is exceeded, the job will skip this execution. This execution will not be defined as an execution failure and will respond in the job log. The value range is from 1 minute to 99 days.

Sys. dbms_scheduler.set_attribute (name => '"SYS". "REBUILD_JOB1"', attribute => 'schedule _ limit ', value => numtodsinterval (20, 'minute '));

 

 

Max_run_duration: set the effective time for job running. If a value is set, the scheduling reports the JOB_OVER_MAX_DUR event when the value is reached, and the event processor determines whether to continue.

Sys. dbms_scheduler.set_attribute (name => '"SYS". "REBUILD_JOB1"', attribute => 'max _ run_duration ', value => numtodsinterval (10, 'minute '));

 

Max_runs: Maximum number of job executions, ranging from 1 to 1000000. The default value is null (that is, the job will be executed repeatedly, or reach the end_date of the job execution, or reach the specified number of failed times ). Once the maximum value is reached, the job will be disable and the status will change to COMPLETED.

Sys. dbms_scheduler.set_attribute (name => '"SYS". "REBUILD_JOB1"', attribute => 'max _ runs', value => 10 );

 

Instance_stickiness: used only for rac databases. The default value is true. If it is set to true, the job runs on the node with the least load. If a node is disabled or the load is too heavy, the new job is not started, instead, there is another node to execute the job.

 

Job_priority: the priority of job execution in the same class. The default value is 3. The range is from 1 to 5.

Sys. dbms_scheduler.set_attribute (name => '"SYS". "REBUILD_JOB1"', attribute => 'job _ priority ', value => 2 );

 

 

Max_failures: number of job failures allowed. The value ranges from 1 to 1000000. The default value is null.

Sys. dbms_scheduler.set_attribute (name => '"SYS". "REBUILD_JOB1"', attribute => 'max _ failures ', value => 5 );

 

Job_weight: If parallel execution technology is used, this parameter can be used to set the degree of parallelism for SQL Execution in the job.

Sys. dbms_scheduler.set_attribute (name => '"SYS". "REBUILD_JOB1"', attribute => 'job _ weight ', value => 5 );