Comprehensive learning of Oracle scheduler features (2) managing jobs

1.2 manage jobs1.2.1 enable jobs

Because the enabled parameter is not explicitly specified during job creation, the job is not automatically executed by default even if start_date is specified. In this case, the DBMS_Scheduler package provides a process enable that can be used to modify the job activation status. The call method is very simple, for example:

SQL> exec dbms_scheduler.enable (evaluate insert _ test_tbl );

PL/SQL procedure successfully completed.

1.2.2 disable jobs

Dbms_scheduler.enable is only used to set the enabling status of a job (not only effective for the job, but also for the chain, program, etc.) to true. If you want to set the enabled status to false? Simple, there is also a process corresponding to this function: dbms_scheduler.disable, for example:

SQL> exec dbms_scheduler.disable (evaluate insert _ test_tbl );

PL/SQL procedure successfully completed.

These two procedures are only used to reset the object state, so they can be executed infinitely, even if the object has been set to the State to be specified during execution.

1.2.3 modify jobs

Due to the large number of job attributes, it is inevitable that you may encounter situations that need to be modified from time to time. For example, if you are not careful when creating a job, the input of the process name to be executed is incorrect (completely possible, create_job does not automatically check whether the specified process is valid during creation. In this regard,SchedulerIt is not as rigorous as a common job. In this case, modifications (or redefinition) to the job will inevitably be involved. No problem. The DBMS_Scheduler package provides a process of set_attribute, can be used to modify the attribute value of a task.

For example, to modify the execution process of the newly created job: insert_test_tbl, the execution statement is as follows:

SQL> exec dbms_scheduler.set_attribute ("insert _ test_tbl", "job _ Action", "P _ insert into test );

PL/SQL procedure successfully completed

Of course, there is no difference between the execution of this statement and the execution of this statement. Here, we will only use an example.

Although the set_attribute process has only three parameters, there are many attribute values that can be modified. The following are some of the frequently used attributes:

• Logging_level: Specifies the log information level recorded for jobs execution.

  SchedulerManagementSpecifically, you can also select the level of information recorded in the log. There are three options:

  • Dbms_scheduler.logging_off: Disable Logging;
  • Dbms_scheduler.logging_runs: records the running information of the task;
  • Dbms_scheduler.logging_full: records all relevant information about the task, including not only the running status of the task, but also the creation and modification of the task.

  Tip: To view jobs managed by scheduler, You can query them in two views: user_scheduler_job_log and user_scheduler_job_run_details.

• Restartable: Specifies whether jobs can be restarted in due time after an error occurs during running.

  If this parameter is not explicitly specified during Task creation, it is set to false by default. If it is set to true, it indicates that an error occurs during task running and the task will still be started at the next running time, in addition, if an error occurs, the job will continue to run again. However, if the connection fails six times, the job will stop.

• Max_failures: specifies the maximum number of consecutive errors of jobs.

  This parameter value ranges from 1 to 1000000. By default, this parameter is set to null, which indicates no limit. After the specified number of errors is reached, the job is automatically disable.

• Max_runs: specifies the maximum number of jobs

  This parameter value can be specified from 1 to 1000000. By default, this parameter is set to null, indicating no limit (only the number of running times is unlimited, and whether the actual job will continue to run, it is still subject to parameters such as end_date and max_failures ). After the specified number of running times is reached, the job will be automatically disable and set to completed.

• Job_type: specifies the type of the job to be executed by the job.

  There are four optional values: "PLSQL _ block", "stored _ procedure", "executable", and "chain.

• Job_action: Specifies the job to be executed by the job.

  The value specified by this parameter depends on the value of job_type. For example, if job_type is set to "stored _ procedure", the parameter value must be the process name in Oracle.

• Start_date: Specifies the initial start time of a job.
• End_date: specifies the time when the job stops running.

This parameter is also associated with auto_drop. If auto_drop is set to true, the job will be automatically deleted once the job stops running. Otherwise, the job will exist, however, the status is changed to completed.

In addition, max_run_duration, job_weight, weight, priority, job_priority, priority, program_name, priority, schedule_name, repeat_interval, job_class, comments, auto_drop, event_spec, raise_events, etc, the meanings of these parameters are not described in detail here. If you are interested, you can refer to the relevant official documents, or wait for this series of articles to be published.

From these configurable attributes, we can see that the jobs managed by scheduler are indeed very flexible. All the parameters mentioned above can be set using the dbms_scheduler.set_attribute process.

Note that apart from manually created jobs,DatabaseJobs may also be automatically created during running. It is not recommended to modify such jobs unless necessary. To determine whether jobs are created by users or automatically by databases, you can use the system column in the * _ scheduler_jobs view. If this column is displayed as true, it indicates that jobs are created by the system.

1.2.4 run jobs

Although most jobs should be executed automatically, after the previous example, we must have known that jobs will be executed automatically after being created, whether the task can be automatically executed is not directly determined by your subjective intention, but by multiple related attributes of jobs.

I believe that after reading the previous content, I should know how to set jobs automatically. The following mainly demonstrates how to manually call jobs and execute jobs. Among them, of course, the DBMS_Scheduler package is indispensable. For example, manually execute the previously created job: insert_test_tbl:

SQL> exec dbms_scheduler.run_job (evaluate insert _ test_tbl );

PL/SQL procedure successfully completed

Jobs will generate a corresponding record in * _ scheduler_job_log for every execution (provided that the logging_level attribute value is not set to dbms_scheduler.logging_off, you can also query the details of job execution in the * _ scheduler_job_run_details view.

1.2.5 stop jobs

You can use dmbs_scheduler.stop_job to stop a job. For example:

SQL> exec dbms_scheduler.stop_job (evaluate insert _ test_tbl );

PL/SQL procedure successfully completed

Note that the stop_job process is not just about updating the job status, but about stopping the currently executed job. If the job you are processing is not currently running, executing the stop_job process triggers a ORA-27366 error.

Stopping jobs also triggers the log information of a task. for jobs that execute the stop operation, the operation in the * _ scheduler_job_log view is recorded as "STOPPED, the information recorded in the additional_info column is similar to "stop job called by user: username.

1.2.6 Delete jobs

It is easy to delete the created job. You can directly execute the dbms_scheduler.drop_job process, for example:

SQL> exec dbms_scheduler.drop_job (evaluate insert _ test_tbl );

PL/SQL procedure successfully completed

Deleting a job does not modify the tag value of a field in the job, but directly deletes its meaning in the data dictionary. Therefore, if the deleted job still needs to be deleted, it can only be rebuilt, however, it cannot be restored quickly in other ways. However, deleting jobs does not cascade the log information that has been executed by these jobs.

========================================================