Comprehensive learning of Oracle scheduler features (1) create jobs

The so-called job is better than job. It is about the new features of oracle after 10 Gb.Scheduler. In10gIn the environment, we recommend that you use scheduler to replace common jobs.ManagementTask execution. In fact, it is too one-sided to describe scheduler as a tool for Job Management. The new scheduler in the 10G version is not just as easy as creating a task ....

Tip: the management of scheduler in Oracle uses the DBMS_Scheduler package. This chapter also uses this as an entry point to describe how to use the DBMS_Scheduler package. It seems that the horse is put upside down. It doesn't matter ," Think notes", I am the master of my website.

1. Use jobs

Jobs are actually the execution scheduling of one or more tasks managed by scheduler.

1.1 create jobs

Jobs are created using the DBMS_Scheduler package, which is the create_job process. When creating a job, you can specify the task to be executed, scheduling information (when to execute, execution cycle, end date, and so on), and other task-related attributes. Create_job process call is relatively simple, for example:

SQL> begin

2 dbms_scheduler.create_job (

3 job_name => 'insert _ test_tbl ',

4 job_type => 'stored _ procedure ',

5 job_action => 'P _ insert into test ',

6 start_date => sysdate,

7 repeat_interval => 'freq = daily; interval = 1 ');

8 end;

9/

The PL/SQL process is successfully completed.

In fact, if you have permissions, you can also create jobs in other schemas. You only need to specify job_name in the format of Schema. job_name. Note: In this case, the created and owner of the created job may be different.

When you create a job using the create_job process, you can specify many parameter values, but in most cases, you can only specify some parameters to meet your needs.

The parameters specified in the preceding example have the following meanings:

• Job_name: the name of the specified task. A required value. Ensure that the specified name is unique.
• Job_type: Operation Type of a task. It is a required value and has the following optional values:
  • Plsql_block: indicates that the task runs a PL/SQL anonymous block.
  • Stored_procedure: indicates that the task executes the Oracle process (including PL/SQL procedure and Java procedure). This parameter value is specified in this example.
  • Executable: indicates that the task runs an external program, such as an operating system command.
  • Chain: indicates that the task runs a chain.
• Job_action: The operation performed by the task. A required value must match the parameter specified in the job_type.

  For example, for PL/SQL anonymous blocks, the specific representatives of PL/SQL blocks can be placed here, similar to declare .. begin .. this type of end. If it is an oracle process, the specific process name should be specified here. Note that, due to task execution, there will be no output even if there are parameters such as out in the process.

• Start_date: specifies the time when the task is executed for the first time. This parameter can be blank. If it is null, the task is executed immediately. The effect is equivalent to specifying this parameter value as sysdate.
• Repeat_interval: Specifies the frequency of task execution, for example, how long it will be triggered for re-execution. This parameter can also be null. If it is null, it indicates that the current set task is only executed once. The repeat_interval parameter needs to be discussed, because it is very different from the interval parameter in the standard job. In contrast, the syntax structure of the repeat_interval parameter is much more complicated. The most important keywords are freq and interval.
  • The freq keyword is used to specify the interval. Optional parameters include: yearly, monthly, weekly, daily, hourly, minutely, and secondly, year, month, week, day, hour, minute, second, and other units.
  • The interval keyword is used to specify the interval frequency. The value range of the specified value ranges from 1 to 99.

  For example: repeat_interval => 'freq = daily; interval = 1'; indicates that the execution is performed once every day. If you change interval to 7, the execution is performed every 7 days. The effect is equivalent to freq = weekly; interval = 1.

  Generally, to create a job using dbms_scheduler.create_job, you must specify at least the first three items in the preceding parameters. You can also specify the following parameters during create_job:

• Number_of_arguments: specifies the number of parameters that need to be included during job execution. The default value is 0. Note that when the job_type column value is plsql_block or chain, this parameter must be set to 0, in either case, parameters are not supported.
• End_date: Specifies the task expiration time. The default value is null. After the task expires, the state of the task is automatically changed to completed, and enabled is set to false. If this parameter is set to null, it indicates that the task will never expire and will be executed repeatedly according to the cycle set by repeat_interval until the set max_runs or max_failures value is reached.
• Job_class: Specifies the class associated with the task. The default value is default_job_class. For information about job class, follow the subsequent articles in this series.
• Enabled: Specifies whether the task is enabled. The default value is false. "False" indicates that the task is not executed unless it is manually called or changed to "true.
• Auto_drop: When this flag is set to true, Oracle will automatically delete the created task when the conditions are met.
  • The task has expired;
  • The maximum number of running tasks has reached the value set by max_runs;
  • The repeat_interval parameter is not specified for the task and only runs once;

  The default value is true. When executing the create_job process, you can manually set this flag to false. When the parameter value is set to false, the task will not be automatically deleted even if the preceding conditions are met, the only situation that can cause the task to be deleted is that the user actively calls the drop_job process.

• Comments: Set the comments of a task. The default value is null.

In the preceding example, a new job is created, but this job is different from a common job. In this case, the user_jobs view cannot be queried and the information of the job just created is not found, because this job is managed by scheduler. To query Jos managed by scheduler, use user_scheduler_jobs (of course, all_scheduler_jobs and dba_scheduler_jobs can also be used). For example:

SQL> select job_name, job_type, job_action, to_char (start_date, 'yyyy-mm-dd hh24: 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, you may find that the job is successfully created but not executed. What is the problem? In fact, the reason is very simple. Do you still remember the enabled parameter mentioned in the create_job process? If it is not explicitly specified, the default value of this parameter is false, and the job will naturally not run. In this case, how can we modify it? Continue to the next section.

 

Http://space.itpub.net/7607759/viewspace-610951