Oracle 10.2資料庫管理員指南-27章

標籤：cte   0.00   values   hang   until   eve   discuss   ora   auto   

27使用發送器

Oracle Database provides database job capabilities through Oracle Scheduler (the Scheduler). This chapter explains how to use the various Scheduler components, and discusses the following topics:

發送器對象和它們的命名

使用Jobs

使用Programs

使用Schedules

使用Job Classes

使用Windows

使用Window Groups

使用Events

使用Chains

分配資源通過jobs

Note:

This chapter describes how to use the DBMS_SCHEDULER package to work with Scheduler components. You can accomplish the same tasks using Oracle Enterprise Manager.

 

 

 

使用jobs

一個job是一個調度和一個程式的結合，還有額外的被程式要求的參數。這一部分給你介紹了基本的job任務和討論下列主題：

Job任務和他們的預存程序

建立jobs

複製jobs

修改jobs

運行jobs

停止jobs

仍掉jobs

關閉jobs

開啟jobs

See Also:

"Jobs" for an overview of jobs.

 

 

 

建立Jobs

你可以建立jobs使用CREATE_JOB預存程序或者EM.當建立一個job，你可以指定一個動作，一個schedule，和其他屬性。例如，下列的語句建立了一個job叫做update_sales，調用了一個儲存的預存程序在OPS模式中來更新一個sales概覽表：

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

   job_name           =>  ‘update_sales‘,

   job_type           =>  ‘STORED_PROCEDURE‘,

   job_action         =>  ‘OPS.SALES_PKG.UPDATE_SALES_SUMMARY‘,

   start_date         =>  ‘28-APR-03 07.00.00 PM Australia/Sydney‘,

   repeat_interval    =>  ‘FREQ=DAILY;INTERVAL=2‘, /* every other day */

   end_date           =>  ‘20-NOV-04 07.00.00 PM Australia/Sydney‘,

   job_class          =>  ‘batch_update_jobs‘,

   comments           =>  ‘My new job‘);

END;

/

你能建立一個job以另外一個schema通過指定schema.job_name.因此，一個job的建立者不一定是job的屬主。Job的屬主是job被建立的模式對應那個使用者，job建立者時正在建立job的使用者。Job被建立使用job被建立的schema的許可權。Job運行時的NLS環境變數是job被建立那一時刻被呈現的NLS環境變數。

 

After a job is created, it can be queried using the *_SCHEDULER_JOBS views. Jobs are created disabled by default and need to be enabled to run.

 

Jobs are set to be automatically dropped by default after they complete. Setting the auto_drop attribute to FALSE causes the job to persist. Note that repeating jobs are not auto-dropped unless the job end date passes, the maximum number of runs (max_runs) is reached, or the maximum number of failures is reached (max_failures).

 

Ways of Creating Jobs

You create a job using the CREATE_JOB procedure or Enterprise Manager. Because this procedure is overloaded, there are several different ways of using it. In addition to inlining a job during the job creation, you can also create a job that points to a named program and schedule. This is discussed in the following sections:

• Creating Jobs Using a Named Program
• Creating Jobs Using a Named Schedule
• Creating Jobs Using a Named Program and Schedule

Creating Jobs Using a Named Program

You can also create a job by pointing to a named program instead of inlining its action. To create a job using a named program, you specify the value for program_name in the CREATE_JOB procedure when creating the job and do not specify the values for job_type, job_action, and number_of_arguments.

To use an existing program when creating a job, the owner of the job must be the owner of the program or have EXECUTE privileges on it. An example of using the CREATE_JOB procedure with a named program is the following statement, which creates a job called my_new_job1:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

   job_name          =>  ‘my_new_job1‘,

   program_name      =>  ‘my_saved_program‘, 

   repeat_interval   =>  ‘FREQ=DAILY;BYHOUR=12‘,

   comments          =>  ‘Daily at noon‘);

END;

/

Creating Jobs Using a Named Schedule

You can also create a job by pointing to a named schedule instead of inlining its schedule. To create a job using a named schedule, you specify the value for schedule_name in the CREATE_JOB procedure when creating the job and do not specify the values for start_date, repeat_interval, and end_date.

You can use any named schedule to create a job because all schedules are created with access to PUBLIC. An example of using the CREATE_JOB procedure with a named schedule is the following statement, which creates a job called my_new_job2:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

   job_name                 =>  ‘my_new_job2‘, 

   job_type                 =>  ‘PLSQL_BLOCK‘,

   job_action               =>  ‘BEGIN SALES_PKG.UPDATE_SALES_SUMMARY; END;‘,

   schedule_name            =>  ‘my_saved_schedule‘);

END;

/

Creating Jobs Using a Named Program and Schedule

A job can also be created by pointing to both a named program and schedule. An example of using the CREATE_JOB procedure with a named program and schedule is the following statement, which creates a new job called my_new_job3 based on the existing program my_saved_program1 and the existing schedule my_saved_schedule1:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

   job_name            =>  ‘my_new_job3‘, 

   program_name        =>  ‘my_saved_program1‘, 

   schedule_name       =>  ‘my_saved_schedule1‘);

END;

/

Copying Jobs

You copy a job using the COPY_JOB procedure or Enterprise Manager. This call copies all the attributes of the old job to the new job except the new job is created disabled and has another name.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the COPY_JOB procedure.

Altering Jobs

You alter a job using the SET_ATTRIBUTE procedure or Enterprise Manager. All jobs can be altered, and, with the exception of the job name, all job attributes can be changed. If there is a running instance of the job when the SET_ATTRIBUTE call is made, it is not affected by the call. The change is only seen in future runs of the job.

In general, you should not alter a job that was automatically created for you by the database. Jobs that were created by the database have the column SYSTEM set to TRUE in job views. The attributes of a job are available in the *_SCHEDULER_JOBS views.

It is perfectly valid for running jobs to alter their own job attributes using the SET_ATTRIBUTE procedure, however, these changes will not be picked up until the next scheduled run of the job.

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE procedure and "Configuring the Scheduler".

Oracle 10.2資料庫管理員指南-27章