oracle job测试,Oracle Job学习与测试

Oracle Job学习与测试

<>P305

>

目录

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在执行)

可用以下语句查询相应的锁信息

Select SID, TYPE, ID1, ID2 FROM V$LOCK WHERE TYPE = ‘JQ’;

当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不能正常执行的原因检查