Oracle Job學習與測試

Oracle Job學習與測試

<>P305

<手冊之JOB>>

 

目錄

1 JOB相關的程式

2 Jobs and Import/Export

3 How Jobs Execute

4 Job Queue Locks

5 常用的Interval

6 Notes

 

1 JOB相關的程式

 

1.1 Job queue(Jnnn) processes execute jobs in the job queue. For each instance, these job queue processes are dynamically spawned by a coordinator job queue(CJQ0) background process. The coordinator periodically selects jobs that are ready to run from the jobs shown in the DBA_JOBS view. It orders them by time, and then spawns Jnnn processes to run the selected jobs. Each Jnnn process executes one of the selected jobs.(後臺程式CJQ0動態地產生Jnnn程式,並從DBA_JOBS中選擇一個Job給產生的Jnnn.由Jnnn程式來執行JOB).

 

1.2 The JOB_QUEUE_PROCESS initialization parameter controls whether a coordinator job queue process is started by an instance.. If this parameter is set to 0, no coordinator job queue process is started at database startup, and consequently no job queue jobs are executed. The JOB_QUEUE_PROCESSES initialization parameter also specifies the maximum number of Jnnn processes that can concurrently run on an instance. The maximum number of processes that can be specified is 1000. (由引數JOB_QUEUE_PROCESS來決定在系統啟動的時候是否啟動CJQ0程式,同時也決定了在系統執行時最多可以產生多少個Jnnn程式)

 

2 Jobs and Import/Export

 

Jobs can be exported and imported. Thus, if you define a job in one database, you can transfer it to another database. When exporting and importing jobs, the job’s number, environment, and definition remain unchanged. If the job number of a job you want to import matches the number of a job already existing in the database, you will not be allowed to import that job. Submit the job as a new job in the database. (Job在Export, Import過程中,Job的屬性不會發生變化,如果JobID已被用,則不能匯入相同JOBID的作業,只有重建)

 

3 How Jobs Execute

 

When a Jnnn process runs a job, the job is run in the same environment in which it was submitted and with the owner’s default privileges. The owner must be explicitly granted the necessary object privileges for all objects referenced within the job definition. (Job Owner 必須擁有JOB所涉及到的物件的相應許可權)

Jnnn processes execute jobs. To execute a job, the process creates a session to run the job. When you force a job to run using the procedure DBMS_JOB.RUN, the job is run by your user process and with your default privileges only. Privileges granted to you through roles are unavailable. You must be explicitly granted the necessary object privileges for all objects referenced within the job definition. (對於DBMS_JOB.RUN所需的許可權,必須是顯式授予的,而不能是透過Role傳遞得到的. )

 

4 Job Queue Locks

 

Oracle uses job queue locks to ensure that a job is executed in only one session at a time. When a job is being run, its session acquires a job queue (JQ) lock for that job. You can use the locking views in the data dictionary to examine information about locks currently held by sessions. (Oracle用鎖來保證每一個Session只有一個Job在執行)

可用以下語句查詢相應的鎖資訊

 

 

當JOB執行錯誤時,Oracle會以一定的間隔來重試執行,間隔的時間也逐步增加,但在嘗試16次後,Oracle將Broken JOB.

 

5 常用的Interval

1 Exactly seven days from the last execution:

SYSDATE + 7

 

2 EVERY HALF HOUR

SYSDATE + 1/48

 

3 Every Monday at 3PM

NEXT_DAY(TRUNC(SYSDATE),’’MONDAY’’) + 15/24

 

4 First Thursday of each quarter

NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,’’Q’’),3),’’THURSDAY’’)

 

對於Interval是從上一次成功執行後開始計算的.假定一個JOB定在每隔7天執行,但某天,沒有執行,則下次執行的時間要在成功執行時間的後7天.

 

6 Notes

 

Job不能正常執行的原因檢查

 http://space.itpub.net/7813229/viewspace-610829