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

At the host level, I want to perform some scheduled tasks, usually using crontab. Convenient and practical, so is there a concept of scheduled tasks in oracle?

The introduction of Oracle Scheduler is used to manage and plan the Job of the database, through which many routine database tasks can be executed automatically and human intervention can be reduced.

Liberating the labor force is essentially the same function as Linux's crontab. But their fields are different. Oracle Scheduler focuses on the automatic management, maintenance and monitoring of Oracle database Job.

After 10g, ORACLE recommends replacing the normal job with Scheduler to manage the execution of the task. In fact, it is too one-sided to describe Scheduler as a tool for managing job.

The new Scheduler in the 10G version is much more than just creating tasks. He also has many powerful functions. Let's introduce them one by one through several chapters.

The so-called JOBS is actually the execution scheduling of one (or more) tasks managed by Scheduler, that is, the person who does the specific work.

1. How to create a job?

Use the DBMS_SCHEDULER package to create a job, and use the CREATE_JOB process in the package to create a job. Users can specify tasks to be executed, scheduling information (when to execute, execution cycle, end date, etc.), and other task-related properties.

Begin

Dbms_scheduler.create_job (

Job_name = > 'INSERT_TEST_TBL'

Job_type = > 'STORED_PROCEDURE'

Job_action = > 'substituted INTERTERTINTOTEST'

Start_date = > sysdate

Repeat_interval = > 'FREQ=DAILY;INTERVAL=1')

END

/

2. Instructions for the creation of job:

When using the CREATE_JOB procedure to create a job, there are many parameter values that can be specified, and most of the time, we just need to know less than that. Here are some key parameters:

Job_name specifies the name of the job, which needs to be unique.

The type of operation performed by the job_type task, which must also be specified here. Available values are:

PLSQL_BLOCK indicates that the task is executing an PL/SQL anonymous block.

STORED_PROCEDURE indicates that the task executes ORACLE stored procedures (including PL/SQL PROCEDURE and JAVA PROCEDURE).

EXECUTABLE indicates that the task executes an external program, such as an operating system command.

CHAIN indicates that the task is executing a CHAIN.

The action performed by the job_action task should match the parameters specified in the JOB_TYPE type. For example, for PL/SQL anonymous blocks, the specific representatives of PL/SQL blocks can be placed here.

Similar to DECLARE.. BEGIN... end; if it is an ORACLE procedure, you should specify a specific procedure name here, and note that because the task is executed, even if there are parameters such as OUT in the procedure, there will be no output when actually executed.

Start_date specifies the time when the task is executed for the first time. This parameter can be empty. If it is empty, the task will be executed immediately, which is equivalent to specifying the parameter value as SYSDATE.

Repeat_interval specifies how often the task will be executed, such as how long it will be triggered to execute again. This parameter can also be empty, which means that the currently set task is executed only once.

The REPEAT_INTERVAL parameter is very different from the INTERVAL parameter in the standard JOB. In contrast, the syntax structure of the REPEAT_INTERVAL parameter is much more complex. The most important keywords are FREQ and INTERVAL.

The FREQ keyword is used to specify the time period of the interval. Optional parameters are: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY, which represent year, month, week, day, hour, minute, second and other units, respectively.

The INTERVAL keyword is used to specify the frequency of intervals, with values ranging from 1 to 99.

REPEAT_INTERVAL= > 'FREQ=DAILY;INTERVAL=1'; means that it is executed once a day, and if you change INTERVAL to 7, it means that it is executed every 7 days, which is equivalent to FREQ=WEEKLY;INTERVAL=1.

Generally speaking, using DBMS_SCHEDULER.CREATE_JOB to create a JOB, the above parameters basically need to be specified. In addition, you can specify the following parameters when CREATE_JOB:

NUMBER_OF_ARGUMENTS specifies the number of parameters that need to be attached when the JOB is executed, and the default value is 0. When the JOB_TYPE column value is PLSQL_BLOCK or CHAIN, this parameter must be set to 0, because incidental parameters are not supported in both cases.

END_DATE specifies the expiration time of the task, and the default value is NULL. When a task expires, the STATE of the task is automatically modified to COMPLETED,ENABLED and set to FALSE. If the parameter is set to empty

Indicates that the task never expires and will be repeated in the period set by the REPEAT_INTERVAL parameter until the set MAX_RUNS or MAX_ values are reached.

JOB_CLASS specifies the CLASS associated with the task, and the default value is DEFAULT_JOB_CLASS.

ENABLED specifies whether the task is enabled, and the default value is FALSE. The FALSE status indicates that the task will not be executed unless it is manually invoked by the user, or the user changes the status of the task to TRUE.

AUTO_DROP when the flag is set to TRUE, ORACLE automatically deletes the created task when the condition is met

Conditions under which the task has expired

The maximum number of runs of the task has reached the set value of MAX_RUNS; the task does not specify a REPEAT_INTERVAL parameter and only runs once.

The default value of this parameter is TRUE. Users can manually specify the flag as FALSE when executing the CREATE_JOB process. When the parameter value is set to FALSE, the task will not be automatically deleted even if the above-mentioned conditions are met. In this case, the only situation that can cause the task to be deleted is that the user actively calls the DROP_JOB procedure.

COMMENTS sets the comment information for the task. The default value is NULL.

The above example creates a new JOB, but this JOB is different from the ordinary JOB. In this case, the information of the newly created JOB cannot be found by querying the USER_JOBS view.

Because this JOB is the JOB managed by SCHEDULER. To query the JOS managed by SCHEDULER, you should use USER_SCHEDULER_JOBS (of course, ALL_SCHEDULER_JOBS and DBA_SCHEDULER_JOBS are also available), for example:

Select job_name,job_type,job_action,to_char (start_date,'yyyy-mm-dd hh34:mi:ss'), repeat_interval,enabled,state from user_scheduler_jobs

JOB_NAME JOB_TYPE JOB_ACTION TO_CHAR (START_DATE, REPEAT_INTERVAL ENABL STATE

-

INSERT_TEST_TBL STORED_PROCEDURE P_INSERTINTOTEST 2009-07-27 13:46:50 FREQ=DAILY;INTERVAL=1 FALSE DISABLED

However, a careful friend may find that although JOB was successfully created, it was not implemented. What is going on? In fact, the reason is very simple, remember the ENABLED parameters mentioned in the previous introduction to the CREATE_JOB process, when not explicitly specified

The default value of this parameter is false,JOB. Naturally, it will not run. If you encounter such a situation, how to modify it?

The following command is required:

Exec dbms_scheduler.enable (- INSERT_TEST_TBL -)

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.