Shulou(Shulou.com)06/01 Report--

Same as above

1.2 manage Jobs1.2.1 enable Jobs

When you created JOB earlier, because the ENABLED parameter was not explicitly specified, JOB does not execute automatically by default, even if START_DATE is specified. In this case, a procedure ENABLE is provided in the DBMS_SCHEDULER package, which can be used to modify the enabled state of JOB, which is very simple to call, such as:

SQL > exec dbms_scheduler.enable (- INSERT_TEST_TBL)

PL/SQL procedure successfully completed.1.2.2 disables Jobs

DBMS_SCHEDULER.ENABLE is only used to set the enabled state of JOB (which is not only valid for JOB, but also for CHAIN, PROGRAM, and so on) to TRUE. If you want to set its enabled status to FALSE? Simple, there is another process corresponding to this feature: DBMS_SCHEDULER.DISABLE, for example:

SQL > exec dbms_scheduler.disable (- INSERT_TEST_TBL)

PL/SQL procedure successfully completed.

Both procedures are used only to reset the state of the object, so both can be executed indefinitely, even if the object is set to the state to be specified at the time of execution.

1.2.3 modify Jobs

Due to the large number of attributes of JOB, it is inevitable to encounter situations that need to be modified from time to time, such as accidentally creating JOB and incorrectly typing the name of the specified process to be executed (it is entirely possible that CREATE_JOB will not automatically check whether the specified process is valid or not. In this respect, SCHEDULER is not as rigorous as ordinary JOB). In this case, modification (or redefinition) of JOB must be involved. No problem, a procedure SET_ATTRIBUTE is provided specifically in the DBMS_SCHEDULER package, which can be used to modify the property values of the task.

For example, modify the procedure executed by the JOB:INSERT_TEST_TBL you just created, and execute the statement as follows:

SQL > exec dbms_scheduler.set_attribute ('INSERT_TEST_TBL','JOB_ACTION','P_ INSERT INTOTEST')

PL/SQL procedure successfully completed

Of course, there is no difference between execution and non-execution of the statement we execute here. We only give an example here, and we will delve into it.

Although the SET_ATTRIBUTE process has only three parameters, there are many attribute values that can be modified. Here are a few of the more commonly used ones:

LOGGING_LEVEL: specifies the level of log information that is logged to jobs execution.

The JOB managed by SCHEDULER specifically records the execution of the task, and users can choose the level of information recorded in the log. There are three choices:

DBMS_SCHEDULER.LOGGING_OFF: disable logging; DBMS_SCHEDULER.LOGGING_RUNS: record the running information of the task; DBMS_SCHEDULER.LOGGING_FULL: record all relevant information about the task, not only the operation of the task, but even the creation and modification of the task will be recorded in the log.

Tip: to view the JOB managed by SCHEDULER, you can query it in USER_SCHEDULER_JOB_LOG and USER_SCHEDULER_JOB_RUN_DETAILS views.

RESTARTABLE: specify whether the jobs can be restarted at the right time after an error occurs

If not explicitly specified when creating a task, this parameter is set to FALSE by default. If it is set to TRUE, it means that when an error occurs when the task is running, it will still start when the next run time arrives, and if the run still makes an error, it will continue to run again, but if the connection error reaches 6 times, the job will stop.

MAX_FAILURES: specifies the maximum number of consecutive errors in jobs

The value of this parameter can be specified from 1 to 1000000, and by default this parameter is set to NULL, which means there is no limit. When the specified number of errors is reached, the job is automatically disable.

MAX_RUNS: specify the maximum number of times jobs is run

The value of this parameter can be specified from 1 to 1000000. By default, this parameter is set to NULL, which means there is no limit on the number of runs. Whether the actual job will continue to run is still subject to the setting of parameters such as end_date and max_failures). After the specified number of runs is reached, the job will also be automatically disable and the status will be set to COMPLETED.

JOB_TYPE: specifies the type of task performed by job

There are four optional values: PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE, and CHAIN.

JOB_ACTION: specify the tasks performed by job

The value specified in this parameter depends on the value in the JOB_TYPE parameter, for example, if JOB_TYPE is set to STORED_PROCEDURE, then the procedure name specified in this parameter must be the name of the procedure in ORACLE.

START_DATE: specify the time when job starts for the first time END_DATE: specify the time when job stops running

This parameter is also associated with AUTO_DROP. If AUTO_DROP is set to TRUE, once the job reaches the time when it stops running, the job will be automatically deleted, otherwise any job exists, but the status is changed to COMPLETED.

In addition, others include MAX_RUN_DURATION,JOB_WEIGHT,INSTANCE_STICKINESS,STOP_ON_WINDOW_CLOSE,JOB_PRIORITY,SCHEDULE_LIMIT,PROGRAM_NAME,NUMBER_OF_ARGUMENTS,SCHEDULE_NAME,REPEAT_INTERVAL,JOB_CLASS,COMMENTS,AUTO_DROP,EVENT_SPEC,RAISE_EVENTS and so on, the meaning represented by these parameters is not detailed here, interested friends can consult the relevant official documents, or wait for the spread of this series of articles, black.

Just from these settable properties, you can see that the job managed by Scheduler is indeed very flexible, and these parameters mentioned above can be set using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure.

It is also important to note that in addition to the jobs created manually by the user, it is also possible for the database to automatically create jobs during operation. Modifications to this type of jobs are not recommended unless necessary. How to distinguish between user-created jobs and automatic database creation can be determined by the SYSTEM column in the * _ SCHEDULER_JOBS view. If the column is displayed as TRUE, it is created by the system.

1.2.4 execute Jobs

Although most jobs should be automatic execution, but after the previous example, we must also realize that it does not mean that the creation of jobs it will be automatically executed, whether it can be truly automatic execution is not directly determined by your subjective will, but by multiple relevant attributes of jobs itself.

On the topic of automatic execution of jobs, I believe that after reading the previous content, you should know how to set up, the following main demonstration, how to manually call jobs and execute, of course, a few jobs _ SCHEDULER package. For example, manually execute the job:INSERT_TEST_TBL you just created earlier:

SQL > exec dbms_scheduler.run_job (- INSERT_TEST_TBL)

PL/SQL procedure successfully completed

Each time Jobs executes, regardless of success or failure, a corresponding record is generated in * _ SCHEDULER_JOB_LOG (provided that the value of LOGGING_LEVEL attribute is not set to DBMS_SCHEDULER.LOGGING_OFF). At the same time, users can query the details of job execution through the * _ SCHEDULER_JOB_RUN_DETAILS view.

1.2.5 stop Jobs

To stop job, you can use the DMBS_SCHEDULER.STOP_JOB process, for example:

SQL > exec dbms_scheduler.stop_job (- INSERT_TEST_TBL)

PL/SQL procedure successfully completed

Note that the STOP_JOB process is not just to update the status of the job, but to stop the currently executing task. If the task you are dealing with is not currently running, executing the STOP_JOB process will trigger an ORA-27366 error.

Stopping Jobs also triggers the log information of a task. For the job that performs the stop operation, the OPERATION of its * _ SCHEDULER_JOB_LOG view is recorded as "STOPPED", and the information recorded in the ADDITIONAL_INFO column is similar to "REASON="Stop job called by user: username".

1.2.6 Delete Jobs

It is relatively simple to delete the created job. You can directly execute the DBMS_SCHEDULER.DROP_JOB process, for example:

SQL > exec dbms_scheduler.drop_job (- INSERT_TEST_TBL)

PL/SQL procedure successfully completed

Deleting a jobs does not modify the tag value of a field in the job, but directly deletes its meaning in the data dictionary, so if the deleted job is found to be still needed in the future, it can only be rebuilt and cannot be quickly recovered by other means. However, deleting the jobs does not cascade delete the log information that the job has performed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.