ITPub博客

首页 > 数据库 > Oracle > Oracle Scheduler学习笔记分享

Oracle Scheduler学习笔记分享

原创 Oracle 作者:oliseh 时间:2015-02-18 23:56:27 0 删除 编辑

Oracle Scheduler学习笔记分享

初次接触oracle scheduler会觉得这东西并不难,但是涉及到方方面面的概念比较多,看似比较分散的知识点间往往又存在较为紧密的关联,技术细节的掌握成为了用好oracle scheduler的关键。下面是我在oracle scheduler学习过程中的总结和提炼出的一些内容,希望对正在学习oracle scheduler的同学有所帮助。如果对基本概念不是很清楚建议还是先看一下oracle的官方文档。

 

第一部分:Oracle scheduler体系结构里的各个对象类型及其使用方法

1.       Program

避免在每个job定义时都在job_action参数定义一长pl/sql代码或者shell命令,也为了在不同的job间实现对相同程序的复用;

 

其中的STORED_PROCEDUREEXTERNAL类型能够接收由define_program_argumentdefine_metadata_argument这两个过程所定义的外部参数类型,PLSQL_BLOCK类型不适用上述两个过程,但能在其代码里直接使用job_namejob_subnamejob_start等变量来引用job执行过程中产生的metadata

 

Program创建时默认为Disable状态,即便在create_job引用到相关program时,这个program仍然可以为Disable状态,直到最后enable job时才会要求program一定处于Enable状态;但是有一种情况例外:当创建job_style=lightweight类型的job时,program必须处于Enable状态,而且对于lightweight job program类型必须是'PLSQL_BLOCK''STORED_PROCEDURE'

 

Detach=yes属性的program在结束之前必须使用END_DETACHED_JOB_RUN通知主job,否则job将一直处于running状态

 

2.       Schedule

执行时刻表,避免在每个job定义时都繁琐的定义start_Dateend_daterepeat_interval等参数,对于执行时间相同的能够共用同一个schedule

 

Scheduler创建完以后就是enabled,且能够被任何用户使用,不需要额外赋权;

 

创建schedulerrepeat_interval参数必须使用Calendaring Expression方式指定,例如每2小时执行1次可以定义为:repeat_interval => 'FREQ=HOURLY; INTERVAL=2;'

不能使用PL/SQL Expression方式指定:repeat_interval=> 'SYSTIMESTAMP + INTERVAL '2' HOUR'

 

Event类型的schedule在创建的时候使用CREATE_EVENT_SCHEDULE里的queue_specevent_condition两个参数指定队列名称及队列中的事件条件,但后续如果要使用set_attribute修改这两个参数时,统一通过event_spec这一个参数进行修改,这个参数有valuevalue2两个值,value对应queue_specvalue2对应event_condition

 

Event类型的schedule所使用的队列如果是一个安全队列的话必须以即将使用这个队列的用户执行DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER定义一个agent,或者以SYS用户执行 DBMS_AQADM.ENABLE_DB_ACCESS也可以达到同样效果

 

3.       Window

Scheduler功能的延伸,在resource manager enabled的情况下能在window窗口打开时切换到一个不同的resource_plan,从而使在该执行窗口运行的job能得到更合理的资源分配。

 

创建Windowduration属性表示每一次窗口打开的持续时间,最小单位为1分钟,end_time属性表示窗口到end_time指定的时间后不在打开,即意味着window变为disabled,并不会强制将End_time之前已经打开但持续时间尚未达到Duration指定值的window关闭。因此当End_time到来的时候仍有可能有window处于open状态,比如下面的例子中W0214_1实际关闭的时间是sysdate+4/1440而不是end_time所定义的sysdate+2/1440

 dbms_scheduler.create_window(window_name=>'W0214_1',resource_plan=>NULL,start_date=>sysdate+1/1440,end_date=>sysdate+2/1440,duration=>interval '3' minute,repeat_interval=>NULL);

 

window overlap的情况要尽可能的避免,因为可能导致到了时间点job无法发起的现象;

 

window创建后默认就是enabled的,只有当前时间超过window定义的end_timewindow所引用的scheduledrop掉的情况下window才会被disabled

 

如果要让window打开时resource_plan不发生变化,必须指定alter system set resoure_manager_plan=force:plan名称,强制系统保留在当前的resource_plan

 

window close时默认情况下不会停止在这个窗口发起的job,除非在创建job时指定了stop_on_window_close=TRUE,但要注意如果windowjob完成前就关闭了,Resource plan就会还原到window打开前的设置值,这样可能使得剩余的job步骤执行时分配不到应有的系统资源,可能引起job执行变慢;

 

4.       Job

要使job能正常调度job_queue_processes一定不能设为0

 

job执行内容有两种方式定义:inline方式、program方式。

inline方式举例如下:

dbms_scheduler.create_job(job_name=>'Job1',job_type=>'EXECUTABLE',job_action=>'/home/oracle/chh1.sh');

program方式举例如下:

dbms_scheduler.create_program(program_name=>'P1',program_action=>'/home/oracle/chh1.sh',program_type=>'EXECUTABLE');

dbms_scheduler.create_job(job_name=>'Job1',program_name=>'Job1');

 

job发起的时间也有两种定义方式:inline方式、named schedule方式

inline方式举例如下:

dbms_scheduler.create_job(job_name=>'Job1',job_type=>'EXECUTABLE',job_action=>'/home/oracle/chh1.sh',start_date=>sysdate+1/1440,end_Date=>sysdate+60/1440,repeat_interval=>'FREQ=MINUTELY');

named schedule方式举例如下:

dbms_scheduler.create_schedule(schedule_name=>'sched1',start_date=>sysdate+1/1440,end_Date=>sysdate+60/1440,repeat_interval=>'FREQ=MINUTELY');

 

dbms_scheduler.create_job(job_name=>'Job1',job_type=>'EXECUTABLE',job_action=>'/home/oracle/chh1.sh',schedule_name=>'sched1');

 

job创建后默认为Disable状态,auto_drop属性默认为TRUEauto_drop=TRUE表示在job运行结束后自动drop掉,但有一种情况例外:当使用named schedule方式定义job的运行时间,且这个schedule是一个window,这种情况下即使auto_drop设为TRUEjob运行完成后也不会被drop掉,比如下面案例中创建出来的j131_3

 dbms_scheduler.create_window(window_name=>'w131_3',resource_plan=>NULL,start_Date=>systimestamp+1/1440,repeat_interval=>NULL,duration=>interval '1' minute);

 

 dbms_scheduler.create_job(job_name=>'j131_3',schedule_name=>'sys.w131_3',job_type=>'PLSQL_BLOCK',job_action=>'insert into t131 values(''j131_3'',systimestamp);commit;',enabled=>TRUE,auto_drop=>TRUE);

 

dba_scheduler_jobs.statecompletedsucceeded间的区别是,completed出现在repeat_interval不为空值的job最后一次被调度执行完成之后,表示这个job所有的调度已经完成。Succeeded只会出现在 repeat_interval为空值、即run-once job完成之后;

 

job在创建之后的默认状态为disabled,除此之外在其所依赖的对象比如program被强行drop掉、在job的调度周期结束后均会被置为disabled状态;

 

job创建时其logging_level默认为OFF,即job运行时不记录任何日志,但是我们从dba_scheduler_job_log等却能看到job的日志,是因为job缺省会分配给名为DEFAULT_JOB_CLASSjob class而这个job classlogging_levelRUNS—表示记录运行期间的日志;

 

使用local/remote external job执行shell脚本的时候一定要在shell脚本里加上Shell解释器,例如:#!/bin/sh,也要在脚本里设定好要用到的环境变量,例如:export TZ=BEIST-8

 

event-based job来说设置parallel_instances=TRUE能够在前一次由event触发的job还在运行时,又有新的event到来后再一次触发job运行,能够及时的响应每一次的event,使一个job的多个instances同时运行,需要注意的是这种情况下的job都是lightweight类型的,我们可以在dba_scheduler_job_log.job_subname列里看到类似于SCHED$_EVTPARINST_X的名称来代表一个lightweight job instance

 

如果A用户要执行B用户下的Lightweight job,那么A用户只要对B用户下的这个lightweight job里所指定的program具有执行权限即可,这一点不同于regular job,如果B用户下的是regular job那么A必须对B用户下的job也要具有执行权限;

 

如果要让job在数据库从异常情况恢复后继续运行,必须设置restartable=TRUE,但如果后续job执行还是失败,且失败次数超过6次后,job就会停止运行,直到下一次调度时间到来后才会再次发起运行

 

dbms_scheduler.run_job能够在job状态为disabled的情况下人工发起job运行,如果job类型为chainjob里包含detached program、或者job是在远程主机或数据库上执行这三种情况下,必须在run_job时指定use_current_session=TRUE,比如dbms_scheduler.run_job(job_name=>'job1',use_current_session=>TRUE),这时虽然是手工运行jobdba_scheduler_jobs视图里的run_countlast_start_date等字段仍然会更新;

 

job_priority能在同一个job class里的不同job间区别优先级,不同job class里的job间没有优先级高低之分。Job_priority在系统资源紧张的时候会保证同一个job class里调度时间相同的两个job,优先级高的比优先级低的先发起,如果系统资源比较空闲,则两个job会在同一时间发起。

 

Set_job_argument_value只能用于传入Varchar类型的参数值,其它类型的参数值可以使用set_job_anydata_value传入,用法如下:

--假设传入timestamp类型

declare

v_agvalue sys.anydata;

begin

v_agvalue:=sys.anydata.converttimestamp(systimestamp);

DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE(job_name=>'LJ0213_3',argument_position=>1,argument_value=>v_agvalue);

end;

/

 

Enable job时会清空dba_scheduler_jobs里的RUN_COUNT,  FAILURE_COUNT , RETRY_COUNT字段以重新开始计数;

 

5.       Job Class

Job class统一创建在SYS用户下,将同一类的job组合在一起,为这个class里的所有job指定resource group,使job能分配到足够执行资源,在RAC环境下还能指定运行在哪个service上以实现在不同RAC节点上执行不同类别job的功能;

 

job classlogging_level和单个job设置的logging_level取值不同时,记录信息多的那个设置生效

 

6.       Chain

多个program的集合,每个program称为一个step,通过rule来定义这些step的执行顺序,要使得chain能够开始执行,必须有一个rulecondition设置为TRUE。要是chain能够正常结束,必须有一个ruleaction'END',否则dba_scheduler_jobs里的state将显示为CHAIN_STALLED,表示chain处于既没有step在运行也没有正常结束的状态;

 

Create_chain里的Evaluation_interval参数,是一个比较有用的选项,能够对chain 定义之外的对象进行定时监测,如果满足条件则可以触发某个step运行,例如下面的定义表示每1分钟监测一次trigtab表的记录数,如果大于0step ST0210_6成功那么开始运行ST0210_5

 dbms_scheduler.create_chain(chain_name=>'cha0210_5',rule_set_name=>NULL,evaluation_interval=>interval '1' minute);

 dbms_scheduler.define_chain_step(chain_name=>'cha0210_5',step_name=>'ST0210_5',program_name=>'P0210_5');

 dbms_scheduler.define_chain_step(chain_name=>'cha0210_5',step_name=>'ST0210_6',program_name=>'P0210_6');

 dbms_scheduler.define_chain_rule(chain_name=>'cha0210_5',condition=>':ST0210_6.state=''SUCCEEDED'' and (select count(*) from trigtab) > 0',action=>'start ST0210_5',rule_name=>'R0210_62');

 

使用ALTER_RUNNING_CHAIN对正在运行的chain job里的step进行属性修改,需要对chain job的修改权限即可,无须对chain本身具有修改权限;

 

一个chain的完整执行过程应包含chain_start(running)->chain_run(succeeded)->chain(completed),下面是从dba_scheduler_job_log里获取的一个chain从开始到结束的执行状态变化过程

select job_name,log_date,operation,status from dba_scheduler_job_log where job_name='J0210_5' order by log_date desc;

 

JOB_NAME LOG_DATE                                           OPERATION       STATUS

-------- -------------------------------------------------- --------------- --------------------

J0210_5  12-FEB-15 10.50.45.205651 AM +08:00                COMPLETED

J0210_5  12-FEB-15 10.50.45.205013 AM +08:00                CHAIN_RUN       SUCCEEDED

J0210_5  12-FEB-15 10.50.45.110554 AM +08:00                RUN             SUCCEEDED

J0210_5  12-FEB-15 10.50.45.006277 AM +08:00                CHAIN_START     RUNNING

Chain runningstart的特点

 

7.       Credential

OSDB层面的用户名和口令定义到访问身份证明里之后对于运行local/remote external jobsremote database jobs时可以直接使用这个身份证明。对于local database jobs执行使用的身份是job owner,所以不需要定义额外的credential

 

8.       Destination

remote database/external jobs运行在哪台主机或者数据库上,必须在remote主机上必须安装scheduler agent,安装方法可以参照在”oracle 11g下安装配置scheduler agent”:http://blog.itpub.net/53956/viewspace-1436615/,完成agent注册后,默认就创建好了external destination,可以从dba_scheduler_external_dests里看到destination名称,但是这样仅满足运行remote external jobs的条件,要运行remote database jobs还必须在job主数据库上用create_database_destination创建出database destination

 

创建remote jobs时一般同时指定credential_name作为缺省的credential,比如:

dbms_scheduler.create_credential(credential_name=>'C0217_2',username=>'autotest',password=>'crm_2013');

dbms_scheduler.create_job(job_name=>'J0217_1',job_action=>'PRC0217_1',job_type=>'STORED_PROCEDURE',end_Date=>systimestamp+300/1440,auto_Drop=>FALSE,destination_name=>'D0217_1',credential_name=>'C0217_2');

如果上面不指定credential_name,那么必须在destination_name前加上credential_name前缀:

dbms_scheduler.create_job(job_name=>'J0217_1',job_action=>'PRC0217_1',job_type=>'STORED_PROCEDURE',end_Date=>systimestamp+300/1440,auto_Drop=>FALSE,destination_name=>' C0217_2.D0217_1');

 

9.       File Watcher

用来探测本地或远程主机指定目录下是否有新文件生成,如果有插入一条事件消息到队列中,基于file watcherjob收到这条消息后便会自动开始运行。去目录下探测是否有新文件生成的工作由SYS用户下名为FILE_WATCHER_SCHEDULEjob来完成,缺省时每隔10分钟探测一次,这一时间间隔可以使用如下方法进行调整: dbms_scheduler.set_attribute('SYS.FILE_WATCHER_SCHEDULE','repeat_interval','FREQ=MINUTELY');最小可以调整为每分钟1次,访问sys.aq$scheduler_filewatcher_qt表可以查看已经入到队列的消息内容;

 

基于file watcherjob在其Parallel_instances属性为FALSE(缺省值)的情况下永远只处理时间戳最新的一个文件,假设FILE_WATCHER_SCHEDULE的时间间隔为1分钟,那么一分钟内目录下陆续到达了3个文件只有时间最新的那个文件会被处理,其它两个会被忽略,如果要一个不落的逐个处理必须将parallel_instances设为TRUE,这样会启动多个instances并行处理每个文件;

 

10.   Group

可以为windowdestination创建group,使用window groupdestination group可以在创建job时为job同时定义包含多个Destinationdestination group作为其destination_name,也可以定义包含多个windowwindow group作为其schedule_name,省去了针对单个destinationwindow进行逐一定义的繁琐

 

 

第二部分:Oracle scheduler里的权限

除非创建时将名称放在双引号里表示严格区分大小,否则创建出来的oracle schedule对象名称都是以大写形式存放在数据字典里的。

job classwindow、类型为window group三类对象其schema都是sys,当引用window和类型为windowgroup时必须在名称前加上SYS。其它对象都是创建在哪个schema下就属于哪个schema

 

Job chainprogram三类对象创建完之后默认均为disabled状态,其余对象建完后都是立即可用的状态

 

Scheduledestinationwindowgroup(window类型)四类对象的使用权限是grantpublic的,所有用户都能使用,比如A用户建完一个名为DestADestinationB用户不用被授予DestA的任何对象权限也能使用DestA

 

对于系统权限的使用归纳如下:

 

System privilege

System privilege能做什么

Create job

在自己的schema下创建jobchainscheduleprogramfile_watchercredentialdestinationgroup

无需任何权限

修改和删除 自己schema下的jobchainscheduleprogramfile_watchercredentialdestinationgroup

Create any job

在别的schema下(SYS除外)创建、修改、运行、删除jobchainscheduleprogramfile_watchercredentialdestinationgroup

Create external job

创建job_action=Executablejob或者指向的program_type=executable类型的job

Create job

Create external job

运行external job

Execute any program

有权使用任何用户下的program

Execute any class

有权使用SYS用户下的所有job class

Manage scheduler

创建/修改/删除 job classwindowgroup(window类型)

Stop_job(job_name=>’job1’,force=>TRUE)—强行终止job

Set_scheduler_attribute –修改scheduler全局属性

Purge_log--清理Scheduler log

set_agent_registration_pass –设置schedule agent连接口令

 

 

关于对象权限的使用归纳如下

Scheduler object

Operation

Object privilege

Job

A用户执行/修改/删除B用户下的job

grant alter on B.jobname to A

Program

A用户使用 B用户下的program

grant execute on B.program_name to A

A用户修改/删除B用户下的program

grant alter on B.program_name to A

chain

A用户使用 B用户下的chain

grant execute on B.chain_name to A

A用户修改/删除B用户下的chain

grant alter on B.chain_name to A

File watcher

A用户使用 B用户下的File watcher

grant execute on B.file_watcher_name to A

A用户修改/删除B用户下的File watcher

grant alter on B. file_watcher_name to A

Credential

A用户使用 B用户下的Credential

grant execute on B.credential_name to A

A用户修改/删除B用户下的Credential

grant alter on B.credential_name to A

Destination

A用户使用B用户下的Destination

无需额外赋权

A用户修改/删除B用户下的Destination

grant alter on B.destination_name to A

Job class

A用户使用SYS用户下的Job class

grant execute on sys. Job_class_name to A

A用户修改/删除SYS用户下的Job class

grant Manage scheduler to A

Schedule

A用户使用B用户下的Schedule

无需额外赋权

A用户修改/删除B用户下的Schedule

grant alter on B.Schedule_name to A

Window

A用户使用SYS用户下的window

无需额外赋权

A用户修改/删除SYS用户下的window

grant Manage scheduler to A

Group(window类型)

A用户使用SYS用户下的group(window类型)

无需额外赋权

A用户修改/删除SYS用户下的group(window类型)

grant Manage scheduler to A

Group(destination类型)

A用户使用B用户下的Group(destination类型)

grant select on group_name to B;

A用户修改/删除B用户下的Group(destination类型)

grant alter on group_name to B;

 

第三部分:一些有助于概念理解的小测试

///////////////////////////////////////////////////////////////////////////////////////////////

/// 111.2版本开始job_queue_processes=0能够禁用cjq进程,当cjq进程被禁用时任何调度都无法启动

////////////////////////////////////////////////////////////////////////////////////////////

11.2版本以前设置job_queue_processes=0,不会禁止调度启动,从11.2开始job_queue_processes=0会使得任何调度都无法启动,因此设置job_queue_processes!=0是确保调度正常运行的首要前提

####设置job_queue_processes=0禁用cjq进程

***job_queue_processes!=0时,ora_cjq0_tstdb1进程存在

tstdb1@jq570322b:/home/tstdb1>ps -ef|grep tstdb1 | grep cjq

  tstdb1 43778098 50725024   0 21:30:21  pts/0  0:00 grep cjq

  tstdb1  7734570        1   0   Jan 11      -  5:07 ora_cjq0_tstdb1

 

SQL> show parameter job_queue_process

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes                  integer     1000

 

***设置job_queue_processes=0后发现cjq进程不在了

SQL> alter system set job_queue_processes=0 scope=memory;

 

System altered.

 

SQL> show parameter job_queue_process

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes                  integer     0

 

tstdb1@jq570322b:/home/tstdb1>ps -ef|grep tstdb1 | grep cjq

  tstdb1  5505512  7996032   0 21:32:33  pts/0  0:00 grep cjq

 

***alert.log里可以看到job_queue_processes被修改的同时,cjq进程被终止

Wed Jan 28 21:32:18 2015

ALTER SYSTEM SET job_queue_processes=0 SCOPE=MEMORY;

Wed Jan 28 21:32:18 2015

Stopping background process CJQ0

 

####设置job_queue_processes=1000重新启用cjq进程

***设置job_queue_processes=1000

SQL> alter system set job_queue_processes=1000 scope=memory;

 

System altered.

 

***立即查看发现cjq进程没有立即启动

tstdb1@jq570322b:/home/tstdb1>ps -ef|grep tstdb1 | grep cjq

  tstdb1  5505512  7996032   0 21:32:33  pts/0  0:00 grep cjq

 

***alert.log里看到过了约4分钟后cjq进程启动

Wed Jan 28 21:39:58 2015

ALTER SYSTEM SET job_queue_processes=1000 SCOPE=MEMORY;

Wed Jan 28 21:43:54 2015

Starting background process CJQ0

Wed Jan 28 21:43:54 2015

CJQ0 started with pid=38, OS id=9634156

 

***OS层面验证cjq进程的启动时间与alert.log的启动时间一致

oracle@jq570322b:/home/oracle>ps -ef|grep tstdb1 | grep cjq

  tstdb1  9634156        1   0 21:43:54      -  0:00 ora_cjq0_tstdb1

 

小提示:cjq0虽是后台进程,但它并不总是常驻的,在没有job运行的情况下会自动退出,在有job即将运行的情况下会自动重启,cjq0进程停止、启动的行为alert.log里有所体现,例如

Tue Feb 03 22:29:57 2015

Stopping background process CJQ0

Tue Feb 03 22:44:51 2015

Starting background process CJQ0

我们可以很容易的模拟出上述情况:将cjq0进程先kill掉,alert.log立即显示Stopping background process CJQ0

然后设置一个即将openwindow,在window open前的几秒钟就能在alert.log里看到Starting background process CJQ0的信息

 

//////////////////////

// 2dba_scheduler_global_attribute.CURRENT_OPEN_WINDOW能看出当前哪个窗口处于active

//////////////////////

SQL> select * from dba_scheduler_global_attribute;

 

ATTRIBUTE_NAME                 VALUE

------------------------------ --------------------

MAX_JOB_SLAVE_PROCESSES

LOG_HISTORY                    30

DEFAULT_TIMEZONE

EMAIL_SERVER

EMAIL_SERVER_ENCRYPTION        NONE

EMAIL_SERVER_CREDENTIAL

EMAIL_SENDER

LAST_OBSERVED_EVENT

EVENT_EXPIRY_TIME

FILE_WATCHER_COUNT             0

CURRENT_OPEN_WINDOW            WEDNESDAY_WINDOW

 

11 rows selected.

 

///////////////////////////////////

// 3dba_scheduler_job_logdba_scheduler_job_run_details内容上的区别

//    dba_Scheduler_window_logdba_Scheduler_window_details内容上的区别

///////////////////////////////////

###dba_scheduler_job_logdba_scheduler_job_run_details内容上的区别

***当前时间为周三14:00,下面的语句设置WEDNESDAY_WINDOW窗口的open时间为14:50

exec dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=14;byminute=50; bysecond=0');

 

***sql tuning advisor自动任务将在14:50发起

col window_next_time format a40

set linesize 140

select * from dba_autotask_window_clients where window_name='WEDNESDAY_WINDOW';

WINDOW_NAME                    WINDOW_NEXT_TIME                         WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M

------------------------------ ---------------------------------------- ----- -------- -------- -------- -------- --------

WEDNESDAY_WINDOW               28-JAN-15 14.50.00.000000 PM +08:00      TRUE  ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

 

***1个小时左右,sql_tuning_advisor自动任务结束后,观察dba_scheduler_job_logdba_scheduler_job_run_details

col job_name format a25

col job_name format a20

col operation format a10

col log_date format a40

set linesize 167

select job_name,job_class,log_date,operation from dba_scheduler_job_log where job_name='ORA$AT_SQ_SQL_SW_817' order by log_date;

JOB_NAME             JOB_CLASS                      LOG_DATE                                 OPERATION

-------------------- ------------------------------ ---------------------------------------- ----------

ORA$AT_SQ_SQL_SW_817 ORA$AT_JCNRM_SQ                28-JAN-15 02.55.00.797278 PM +08:00      UPDATE

ORA$AT_SQ_SQL_SW_817 ORA$AT_JCNRM_SQ                28-JAN-15 02.55.00.807214 PM +08:00      ENABLE

ORA$AT_SQ_SQL_SW_817 ORA$AT_JCNRM_SQ                28-JAN-15 03.55.05.780034 PM +08:00      RUN

ORA$AT_SQ_SQL_SW_817 ORA$AT_JCNRM_SQ                28-JAN-15 03.55.05.780947 PM +08:00      COMPLETED

ORA$AT_SQ_SQL_SW_817 ORA$AT_JCNRM_SQ                28-JAN-15 03.55.05.781699 PM +08:00      DROP

 

SQL> select job_class_name,RESOURCE_CONSUMER_GROUP,LOGGING_LEVEL from dba_scheduler_job_classes where job_class_name='ORA$AT_JCNRM_SQ';

 

JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP        LOGGING_LEV

------------------------------ ------------------------------ -----------

ORA$AT_JCNRM_SQ                ORA$AUTOTASK_SQL_GROUP         FULL

 

col job_name format a30

col status format a10

col log_date format a30

col actual_start_date format a30

col run_duration format a16

select job_name,log_date,status,actual_start_Date,run_duration from dba_scheduler_job_run_details where job_name='ORA$AT_SQ_SQL_SW_817' order by log_date;

JOB_NAME                       LOG_DATE                       STATUS     ACTUAL_START_DATE              RUN_DURATION

------------------------------ ------------------------------ ---------- ------------------------------ ----------------

ORA$AT_SQ_SQL_SW_817           28-JAN-15 03.55.05.780501 PM + SUCCEEDED  28-JAN-15 02.55.02.707063 PM + +000 01:00:03

                               08:00                                     08:00

 

sql tuning advisor自动任务执行时派生出的job名为AT_SQ_SQL_SW_817AT_SQ_SQL_SW_817归属于名为ORA$AT_JCNRM_SQjob classesORA$AT_JCNRM_SQlog_levelFULL,所以我们看到在dba_scheduler_job_log里记录了所有关于job ORA$AT_SQ_SQL_SW_817的操作记录,包括update->enable->run->completed->drop五个阶段,其中update对应我们设置Schedule的操作:exec dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=14;byminute=50; bysecond=0'); enable操作是因为我们在set_attribute的时候系统会自动把WEDNESDAY_WINDOW先置为disable,修改完attribute后,系统重新enable WEDNESDAY_WINDOWdisableenable的操作都是oracle自动完成的,run->completed->drop三个阶段反应了job运行、job运行完成后把自己drop掉的过程。而在dba_scheduler_job_run_details里只有一行有关ORA$AT_SQ_SQL_SW_817执行结果的记录,这条记录只有在ORA$AT_SQ_SQL_SW_817执行完成后才能看到,并且包含了实际执行时间、执行时长等较为丰富的时间明细

 

###在执行上述sql tuning advisor自动任务的过程中dba_Scheduler_window_logdba_Scheduler_window_details内容上的区别

select log_date,window_name,operation from dba_scheduler_window_log order by log_date;

LOG_DATE                                 WINDOW_NAME                                                       OPERATION

---------------------------------------- ----------------------------------------------------------------- ----------

28-JAN-15 02.53.22.222563 PM +08:00      WEDNESDAY_WINDOW                                                  UPDATE

28-JAN-15 02.55.00.644519 PM +08:00      WEDNESDAY_WINDOW                                                  OPEN

28-JAN-15 06.55.00.018390 PM +08:00      WEDNESDAY_WINDOW                                                  CLOSE

 

col log_Date format a30

col req_start_date format a30

col actual_start_date format a30

col actual_duration format a30

col window_duration format a30

col window_name format a10

set linesize 150

set pagesize 200

 

select log_date,window_name,req_start_date,actual_start_date,window_duration,actual_duration from Dba_Scheduler_Window_Details where window_name='WEDNESDAY_WINDOW' order by log_date desc;

LOG_DATE                       WINDOW_NAM REQ_START_DATE                 ACTUAL_START_DATE              WINDOW_DURATION

------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------

ACTUAL_DURATION

------------------------------

28-JAN-15 06.55.00.018998 PM + WEDNESDAY_ 28-JAN-15 02.55.00.000000 PM + 28-JAN-15 02.55.00.302413 PM + +000 04:00:00

08:00                          WINDOW     08:00                          08:00

+000 04:00:00

 

dba_scheduler_window_log里也包含了三个步骤update(更新WEDNESDAY_WINDOW属性)->open->close,这里的close时间总是要大于等于AT_SQ_SQL_SW_817 job完成的时间。与dba_scheduler_job_run_details类似,Dba_Scheduler_Window_Details只含有一条记录,且只有等window close后才会出现,表示了整个窗口的时间跨度。

dba_scheduler_job_run_details.ADDITIONAL_INFO字段里显示了job运行出错的完整原因,ERROR里显示了ORA错误号

 

///////////////////////////////////

// 4、哪些视图可以反映出autotask的执行过程与执行结果

///////////////////////////////////

执行过程:

select * from dba_autotask_client_job;

select * from v$advisor_progress;

执行结果:

dba_autotask_client_history;

dba_autotask_job_history;

dba_scheduler_job_log;

dba_scheduler_job_run_details;

 

///////////////////////////////////

// 5stop_on_window_close=TRUE/FALSE决定了window close的时候job是否会终止

///////////////////////////////////

###stop_on_window_close=TRUEclose windowjob也随之停止

--创建测试表

create table t0202_2 (c1 varchar2(10),tm timestamp);

 

--create job

create or replace procedure prc0202_2

is

begin

while ( true )

loop

insert into t0202_2 values('J0202_2',systimestamp);

dbms_lock.sleep(5);

commit;

end loop;

end;

/

 

truncate table t0202_2;

exec dbms_scheduler.drop_job(job_name=>'J0202_2');

exec dbms_scheduler.drop_window(window_name=>'w0202_2',force=>TRUE);

exec dbms_Scheduler.drop_schedule(schedule_name=>'s0202_2');

exec dbms_Scheduler.purge_log(job_name=>'J0202_2');

exec dbms_scheduler.drop_program(program_name=>'P0202_2');

 

exec dbms_scheduler.create_program(program_name=>'P0202_2',program_type=>'STORED_PROCEDURE',program_action=>'prc0202_2');

 

exec dbms_scheduler.create_schedule(schedule_name=>'s0202_2',start_date=>systimestamp+1/1440,repeat_interval=>NULL,end_date=>systimestamp+10/1440);

 

exec dbms_scheduler.create_window(window_name=>'w0202_2',resource_plan=>'MIXED_WORKLOAD_PLAN',schedule_name=>'s0202_2',duration=>interval '10' minute);

 

exec dbms_scheduler.create_job(job_name=>'J0202_2',program_name=>'P0202_2',schedule_name=>'sys.w0202_2',auto_drop=>FALSE);

 

exec dbms_scheduler.set_attribute('J0202_2','stop_on_window_close',TRUE);

 

exec dbms_scheduler.enable('P0202_2,J0202_2');

 

--W0202_2处于open状态

select * from dba_scheduler_global_attribute where attribute_name='CURRENT_OPEN_WINDOW';

ATTRIBUTE_NAME                 VALUE

------------------------------ --------------------

CURRENT_OPEN_WINDOW            W0202_2

 

--5秒钟一次有记录进入t0202_2,说明job在正常运行

select count(*) from t0202_2

 

  COUNT(*)

----------

         1

 

select count(*) from t0202_2

 

  COUNT(*)

----------

         2

 

select count(*) from t0202_2

 

  COUNT(*)

----------

         3

 

--closew0202_2后,t0202_2表记录停止更新                     

exec dbms_scheduler.close_window(window_name=>'w0202_2');

 

select count(*) from t0202_2

 

  COUNT(*)

----------

         6

 

select count(*) from t0202_2

 

  COUNT(*)

----------

         6

 

select count(*) from t0202_2

 

  COUNT(*)

----------

         6

 

--dba_Scheduler_job_run_details显示J0202_2停止的原因是"Stop job called because associated window was closed"

col job_name format a20

col status format a30

col additional_info format a80

set linesize 170

select job_name,additional_info,status from dba_Scheduler_job_run_details where job_name='J0202_2';

JOB_NAME             ADDITIONAL_INFO                                                                  STATUS

-------------------- -------------------------------------------------------------------------------- ------------------------------

J0202_2              REASON="Stop job called because associated window was closed"                    STOPPED

 

###stop_on_window_close=FALSEclose windowjob继续运行

--在上述测试的基础上,设置stop_on_window_close=FALSE

truncate table t0202_2;

exec dbms_scheduler.set_attribute('s0202_2','start_date',systimestamp+1/1440);

exec dbms_Scheduler.set_attribute('s0202_2','end_date',systimestamp+10/1440);

exec dbms_Scheduler.enable('sys.W0202_2');

exec dbms_Scheduler.set_attribute('j0202_2','stop_on_window_close',FALSE);

 

--W0202_2窗口打开时的状态

col value format a40

set linesize 120

select * from dba_scheduler_global_attribute where attribute_name='CURRENT_OPEN_WINDOW';

ATTRIBUTE_NAME                 VALUE

------------------------------ ----------------------------------------

CURRENT_OPEN_WINDOW            W0202_2

 

SQL> select count(*) from t0202_2;

 

  COUNT(*)

----------

        10

 

SQL> select count(*) from t0202_2;

 

  COUNT(*)

----------

        11

 

--close window

SQL> exec dbms_Scheduler.close_window('W0202_2');

 

PL/SQL procedure successfully completed.

 

SQL> select * from dba_scheduler_global_attribute where attribute_name='CURRENT_OPEN_WINDOW';

 

ATTRIBUTE_NAME                 VALUE

------------------------------ --------------------

CURRENT_OPEN_WINDOW

 

--t0202_2记录仍然在增长

SQL> select count(*) from t0202_2;

 

  COUNT(*)

----------

        31

 

SQL> select count(*) from t0202_2;

 

  COUNT(*)

----------

        32

                       

///////////////////////////////////

// 6define_program_argument用于inline方式定义job_action和非inline方式定义job_actionjob,这两种情况下有何区别

///////////////////////////////////

create or replace procedure prc0204_2 (v_info in varchar2)

as

begin

dbms_output.put_line(v_info);

end;

/

 

###inlined program方式定义job_actionjob_type='STORED_PROCEDURE'的情况

exec dbms_scheduler.create_job(job_name=>'J0204_2',job_type=>'STORED_PROCEDURE',job_action=>'prc0204_2',number_of_arguments=>1);

exec dbms_scheduler.set_job_argument_value(job_name=>'J0204_2',argument_position=>1,argument_value=>'aaa');

 

set serveroutput on

SQL>exec dbms_scheduler.run_job('J0204_2');

aaa

 

###inlined program方式定义job_actionprogram_type='STORED_PROCEDURE'的情况,多了一步define_program_argument,且非inline方式可以利用default_value指定缺省值,亦能指定argument_name

exec dbms_scheduler.create_program(program_name=>'P0204_2',program_action=>'PRC0204_2',program_type=>'STORED_PROCEDURE',number_of_arguments=>1);

exec dbms_scheduler.define_program_argument(program_name=>'P0204_2',argument_position=>1,argument_type=>'varchar2',default_value=>'aaa');

exec dbms_scheduler.create_job(job_name=>'J0204_3',program_name=>'P0204_2');

exec dbms_scheduler.enable('P0204_2');

 

set serveroutput on

exec dbms_scheduler.set_job_argument_value(job_name=>'J0204_3',argument_position=>1,argument_value=>'bbb');

SQL> exec dbms_scheduler.run_job('J0204_3');

bbb

 

---不指定参数值,则输出缺省值

exec dbms_scheduler.RESET_JOB_ARGUMENT_VALUE(job_name=>'J0204_3',argument_position=>1);

set serveroutput on

SQL> exec dbms_scheduler.run_job('J0204_3');

aaa

 

PL/SQL procedure successfully completed.

 

###inlined program方式定义的job_type=>'PLSQL_BLOCK'job,以及非inlined program方式定义的program_type='PLSQL_BLOCK'job,无法使用define_program_argument&define_metadata_argument来定义参数,却可以直接使用define_metadata_argumentmetadata_attribute中除了event_message以外字段,包括job_namejob_start等,举个例子,这个例子属于"inlined program方式定义的program_type='PLSQL_BLOCK'job"

exec dbms_scheduler.drop_job(job_name=>'J0204_7');

exec dbms_scheduler.drop_program(program_name=>'P0204_7');

exec dbms_scheduler.create_program(program_name=>'P0204_7',program_action=>'BEGIN dbms_output.put_line(job_name||'':''||job_start);end;',program_type=>'PLSQL_BLOCK');

exec dbms_scheduler.create_job(job_name=>'J0204_7',program_name=>'P0204_7');

exec dbms_scheduler.enable('P0204_7');

set serveroutput on

SQL> exec dbms_scheduler.run_job('J0204_7');

J0204_7:05-FEB-15 01.08.55.145163 PM +08:00

 

PL/SQL procedure successfully completed.

 

在上面的输出中我们字节在plsql block里引用了job_namejob_start 两个metadata字段,输出了job的名称和执行开始时间

 

///////////////////////////////////

// 7、如果遇到program里的参数不是varchar2类型的,那么必须使用SET_JOB_ANYDATA_VALUE来赋值

///////////////////////////////////

exec dbms_Scheduler.drop_job('LJ0213_3');

exec dbms_Scheduler.drop_program('P0213_3');

 

--创建procedureprogramjob等对象

create or replace procedure PRC0213_3(v_str in timestamp)

as

begin

dbms_output.put_line(to_char(v_str,'yyyymmdd hh24:mi:ss.FF'));

dbms_lock.sleep(1);

end;

/

 

exec dbms_Scheduler.create_program(program_name=>'P0213_3',program_type=>'STORED_PROCEDURE',program_action=>'PRC0213_3',number_of_arguments=>1);

--exec dbms_Scheduler.define_program_argument(program_name=>'P0213_3',argument_position=>1,argument_type=>'VARCHAR2');

exec DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(program_name=>'P0213_3',argument_position=>1,argument_type=>'timestamp');

exec dbms_Scheduler.enable('P0213_3');

exec dbms_Scheduler.create_job(job_name=>'LJ0213_3',program_name=>'P0213_3',job_style=>'regular',auto_Drop=>FALSE);

 

---设置sys.anydata类型的变量,作为入参传入SET_JOB_ANYDATA_VALUE

declare

v_agvalue sys.anydata;

begin

v_agvalue:=sys.anydata.converttimestamp(systimestamp);

DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE(job_name=>'LJ0213_3',argument_position=>1,argument_value=>v_agvalue);

end;

/

 

---执行job

set serveroutput on

exec dbms_Scheduler.run_job('LJ0213_3');

 

20150213 12:12:45.601728000

 

PL/SQL procedure successfully completed.

 

///////////////////////////////////

// 8、创建一个detached job

///////////////////////////////////

使用detached job关键在于使用end_detached_job_rundetached job的执行结果通知主job

---创建测试表

create table t0204_4 (c1 timestamp);

 

---创建procedureprocedure里没有包含通知主job的步骤

create or replace procedure prc0204_4

as

begin

insert into t0204_4 values(systimestamp);

commit;

--DBMS_SCHEDULER.END_DETACHED_JOB_RUN(job_name=>'J0204_4',error_number=>0,additional_info=>'detached job is successful'); -->应该在这里调用END_DETACHED_JOB_RUN,以通知主job是否完成,这里故意不加看下job执行后的效果

end;

/

 

--创建programjob

exec dbms_scheduler.create_program(program_name=>'P0204_5',program_type=>'STORED_PROCEDURE',program_action=>'PRC0204_4');

exec dbms_scheduler.set_attribute('P0204_5','detached',TRUE);

exec dbms_scheduler.create_job(job_name=>'J0204_4',program_name=>'P0204_5');

exec dbms_scheduler.enable('P0204_5');

 

---如果用run_job运行一个detached job,必须指定use_current_session=FALSE

exec dbms_scheduler.run_job('J0204_4',use_current_session=>FALSE);

 

SQL> select * from t0204_4;

 

C1

---------------------------------------------------------------------------

04-FEB-15 02.52.01.371199 PM

 

---查看job状态,因为program里没有运行END_DETACHED_JOB_RUN,所以job依然是running状态

col job_name format a10

col program_name format a10

col state format a20

col last_start_date format a40

col last_run_duration format a40

set linesize 150

SQL> select job_name,program_name,state,last_start_date,last_run_duration from dba_scheduler_jobs where job_name='J0204_4';

 

JOB_NAME   PROGRAM_NA STATE                LAST_START_DATE                          LAST_RUN_DURATION

---------- ---------- -------------------- ---------------------------------------- ----------------------------------------

J0204_4    P0204_5    RUNNING              04-FEB-15 02.52.01.339706 PM +08:00

 

---手动执行END_DETACHED_JOB_RUNdba_scheduler_job_log等视图反应出J0204_4已经正常结束

exec DBMS_SCHEDULER.END_DETACHED_JOB_RUN(job_name=>'J0204_4',error_number=>0,additional_info=>'detached job is successful');

 

col job_name format a10

col program_name format a10

col state format a20

col last_start_date format a40

col last_run_duration format a40

set linesize 150

SQL> select job_name,program_name,state,last_start_date,last_run_duration from dba_scheduler_jobs where job_name='J0204_4';

 

JOB_NAME   PROGRAM_NA STATE                LAST_START_DATE                          LAST_RUN_DURATION

---------- ---------- -------------------- ---------------------------------------- ----------------------------------------

J0204_4    P0204_5    DISABLED             04-FEB-15 02.52.01.339706 PM +08:00      +000000000 00:14:25.815847

 

col log_date format a30

col owner format a10

col job_name format a10

col operation format a10

col status format a10

set linesize 120

select log_date,job_name,operation,status from dba_scheduler_job_log where job_name='J0204_4' order by log_date desc;

 

LOG_DATE                       JOB_NAME   OPERATION  STATUS

------------------------------ ---------- ---------- ----------

04-FEB-15 03.06.27.156039 PM + J0204_4    RUN        SUCCEEDED

08:00

 

col job_name format a30

col status format a10

col log_date format a40

col actual_start_date format a40

col run_duration format a16

set linesize 150

select job_name,log_date,status,actual_start_Date,run_duration from dba_scheduler_job_run_details where job_name='J0204_4' order by log_date;

JOB_NAME                       LOG_DATE                       STATUS     ACTUAL_START_DATE              RUN_DURATION

------------------------------ ------------------------------ ---------- ------------------------------ ----------------

J0204_4                        04-FEB-15 03.06.27.156913 PM +08:00      SUCCEEDED  04-FEB-15 02.52.01.339706 PM +08:00      +000 00:14:26

 

///////////////////////////////////

// 9、创建一个lightweight job

///////////////////////////////////

lightweight jobjob_action必须定义为一个program_nameprogram_type必须为PLSQL_BLOCK或者STORED_PROCEDURElightweight job的对象权限继承于program的权限:

grant connect,resource,create job to scott identified by "asdf3_14";

grant connect,resource,create job to scott2 identified by "asdf3_14";

 

sqlplus scott/773946

 

create table t0204_6 (c1 timestamp);

 

create or replace procedure prc0204_6

as

begin

insert into t0204_6 values(systimestamp);

commit;

end;

/

 

exec dbms_scheduler.create_program(program_name=>'P0204_6',program_action=>'PRC0204_6',program_type=>'STORED_PROCEDURE');

 

---对于lightweight job必须先enable program,再create_job

exec dbms_scheduler.enable('P0204_6');

exec dbms_scheduler.create_job(job_name=>'J0204_6',program_name=>'P0204_6',job_style=>'LIGHTWEIGHT');

select * from t0204_6;

 

---lightweight job不是Schema objects,因此在dba_object里是查不到的

SQL> select count(*) from dba_objects where object_name='J0204_6';

 

  COUNT(*)

----------

         0

 

---正因为lightweight job不是Schema objects,所以scott2scott3都有权限执行J0204_6,当然scott2Scott3用户必须对J0204_6所调用的program具有执行权限

sqlplus scott2/773946

grant execute on scott.P0204_6 to scott2;

exec dbms_scheduler.run_job(job_name=>'scott.J0204_6');

 

ORA-00600: internal error code, arguments: [kssadd:  null parent], [], [], [],

 

上述问题正在提问metalink

 

///////////////////////////////////

// 10、创建一个chain

///////////////////////////////////

创建一个chain名为cha0205_1,其中包含以下一些对象:

J0205_0:这是一个job,该job成功完成后会生成events插入到SYS.SCHEDULER$_EVENT_QUEUE队列,用于触发后面名为est0205_1event schedule

cha0205_1:这是一个chain,里面包含了st0205_1st0205_2st0205_3st0205_4四个chain step

est0205_1:这是一个基于eventschedule,由J0205_0成功完成后触发

st0205_2,这是cha0205_1里基于eventstep,由名为est0205_1event schedule触发

st0205_3,这是cha0205_1里的program step

st0205_4,这是cha0205_1里的program step

 

st0205_1cha0205_1里的第一个步骤,当start st0205_1的时候,整个chain会等待est0205_1里所包含的event发生,这里的event具体就是J0205_0这个job是否成功完成,如果J0205_0能成功完成,那么就会触发est0205_1,进而使得st0205_1开始运行,这时整个chain才真正运转起来,st0205_1的成功执行又会触发st0205_2的运行,从st0205_2开始的执行逻辑是:

如果st0205_2执行失败,则运行st0205_4,一旦st0205_4运行结束,无论结果是成功还是失败,整个chain到此结束;

如果st0205_2执行成功,则运行st0205_3,一旦st0205_3运行结束,无论结果是成功还是失败,整个chain到此结束;

 

---赋于ad用户创建chain所需的权限

grant create job,create rule,create evaluation context,create rule set to ad;

 

---ad用户建立测试表

create table t0205_2 (c1 varchar2(100),c2 varchar2(100),tm timestamp);

create table t0205_34 (c1 varchar2(100),c2 varchar2(100),tm timestamp);

 

---创建program、以及program要用到的存储过程

create or replace procedure PRC0205_2(v_jname in varchar2,v_jsubname in varchar2)

as

begin

insert into t0205_2 values(v_jname,v_jsubname,systimestamp);

commit;

end;

/

 

create or replace procedure PRC0205_34(v_jname in varchar2,v_jsubname in varchar2)

as

begin

insert into t0205_34 values(v_jname,v_jsubname,systimestamp);

commit;

end;

/

 

exec dbms_scheduler.create_program(program_name=>'P0205_2',program_action=>'PRC0205_2',program_type=>'STORED_PROCEDURE',number_of_arguments=>2);

exec dbms_scheduler.create_program(program_name=>'P0205_34',program_action=>'PRC0205_34',program_type=>'STORED_PROCEDURE',number_of_arguments=>2);

 

exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_2',metadata_attribute=>'job_name',argument_position=>1);

exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_2',metadata_attribute=>'job_subname',argument_position=>2);

exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_34',metadata_attribute=>'job_name',argument_position=>1);

exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_34',metadata_attribute=>'job_subname',argument_position=>2);

 

 

---创建chain

exec dbms_scheduler.create_chain(chain_name=>'cha0205_1',rule_set_name=>NULL,evaluation_interval=>NULL);

 

---SYS.SCHEDULER$_EVENT_QUEUE队列里为ad用户创建一个ad_agent用于存放所有ad用户生成的Event

exec DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER('ad_agent');

 

---创建event schedule,触发条件是J0205_0成功完成

exec dbms_scheduler.create_event_schedule(schedule_name=>'est0205_1',start_date=>systimestamp+1/1440,end_date=>systimestamp+100/1440,event_condition=>'tab.user_data.event_type=''JOB_SUCCEEDED'' and tab.user_data.OBJECT_NAME=''J0205_0''',queue_spec=>'SYS.SCHEDULER$_EVENT_QUEUE,ad_agent');

 

---创建event step : st0205_1st0205_1Schedule:est0205_1触发

exec dbms_scheduler.define_chain_event_step(chain_name=>'cha0205_1',step_name=>'st0205_1',event_schedule_name=>'est0205_1');

 

---创建event step : st0205_2st0205_3st0205_4

exec dbms_scheduler.define_chain_step(chain_name=>'cha0205_1',step_name=>'st0205_2',program_name=>'P0205_2');

exec dbms_scheduler.define_chain_step(chain_name=>'cha0205_1',step_name=>'st0205_3',program_name=>'P0205_34');

exec dbms_scheduler.define_chain_step(chain_name=>'cha0205_1',step_name=>'st0205_4',program_name=>'P0205_34');

 

---定义chain rule:r0205_1,仅当st0205_1.state='SUCCEEDED'时才启动st0205_2

exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>'TRUE',action=>'start st0205_1',rule_name=>'r0205_0');

exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>':st0205_1.state=''SUCCEEDED''',action=>'start st0205_2',rule_name=>'r0205_1');

 

---定义chain rule:r0205_2s,当st0205_2.state='SUCCEEDED'时启动st0205_3

exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>':st0205_2.state=''SUCCEEDED''',action=>'start st0205_3',rule_name=>'r0205_2s');

 

---定义chain rule:r0205_2f,当st0205_2.state='FAILED'时启动st0205_4

exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>':st0205_2.state=''FAILED''',action=>'start st0205_4',rule_name=>'r0205_2f');

 

---定义chain rule:r0205_3,当st0205_3.state='COMPLETED'END

exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>':st0205_3.completed=''TRUE''',action=>'end',rule_name=>'r0205_3');

 

---定义chain rule:r0205_4,当st0205_4.state='COMPLETED'END

exec dbms_scheduler.define_chain_rule(chain_name=>'cha0205_1',condition=>':st0205_4.completed=''TRUE''',action=>'end',rule_name=>'r0205_4');

 

---创建job_type=chainJ0205_7,每1分钟执行一次

exec dbms_Scheduler.create_job(job_name=>'J0205_7',job_type=>'chain',job_action=>'cha0205_1',start_date=>systimestamp+1/1440,repeat_interval=>'systimestamp+interval ''1'' minute',end_date=>systimestamp+100/1440,auto_Drop=>FALSE);

 

---创建job_type=chainJ0205_0,并为J0205_0定义raised_event

exec dbms_Scheduler.create_job(job_name=>'J0205_0',job_type=>'PLSQL_BLOCK',job_action=>'BEGIN dbms_output.put_line(job_name||''executed'');END;',auto_Drop=>FALSE);

exec dbms_Scheduler.set_attribute('J0205_0','raise_events',DBMS_SCHEDULER.JOB_SUCCEEDED);

 

---enable除了J0205_0之外的其它对象,J0205_0准备通过run_job的方式手动运行,所以此处不必enable J0205_0

exec dbms_Scheduler.enable('P0205_2,P0205_34,cha0205_1,J0205_7');

 

---手工运行J0205_7,dba_scheduler_jobs视图发现J0205_7正处于running状态,但其中的第一个step ST0205_1处在scheduled状态,即正在等待

set serveroutput on

exec dbms_Scheduler.run_job('J0205_7',FALSE);

col last_start_date format a40

col next_run_date format a40

col repeat_interval format a40

set linesize 170

select job_name,job_subname,last_start_date,next_run_date,repeat_interval,enabled,state from dba_scheduler_jobs where job_name='J0205_7';

 

JOB_NAME   JOB_SUBNAM LAST_START_DATE                          NEXT_RUN_DATE                            REPEAT_INTERVAL                          ENABL STATE

---------- ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----- ----------

J0205_7    ST0205_1                                                                                                                              TRUE  SCHEDULED

J0205_7               08-FEB-15 08.52.23.719069 AM +08:00      08-FEB-15 08.53.11.000000 AM +08:00      systimestamp+interval '1' minute         TRUE  RUNNING

 

 

---对于job_subname='ST0205_1'step可以清楚的看到它在等待SCHEDULER$_EVENT_QUEUE里符合tab.user_data.event_type='JOB_SUCCEEDED' and tab.user_data.OBJECT_NAME='J0205_0'的事件

col job_name format a10

col job_subname format a10

col enabled format a10

col state format a10

col EVENT_QUEUE_NAME   format a20

col EVENT_QUEUE_owner  format a20

col EVENT_QUEUE_agent  format a20

col event_condition    format a20

set linesize 180

select job_name,job_subname,enabled,state,EVENT_QUEUE_NAME,EVENT_QUEUE_owner,EVENT_QUEUE_agent,event_condition from dba_scheduler_jobs where job_name='J0205_7' and job_subname='ST0205_1';

 

 

JOB_NAME   JOB_SUBNAM ENABLED    STATE      EVENT_QUEUE_NAME     EVENT_QUEUE_OWNER    EVENT_QUEUE_AGENT    EVENT_CONDITION

---------- ---------- ---------- ---------- -------------------- -------------------- -------------------- --------------------

J0205_7    ST0205_1   TRUE       SCHEDULED  SCHEDULER$_EVENT_QUE SYS                  AD_AGENT             tab.user_data.event_

                                            UE                                                             type='JOB_SUCCEEDED'

                                                                                                            and tab.user_data.O

                                                                                                           BJECT_NAME='J0205_0'

                                                                                                          

---DBA_SCHEDULER_RUNNING_CHAINS里也显示四个step都没有开始执行

col state format a20

select job_name,chain_name,state,step_name from DBA_SCHEDULER_RUNNING_CHAINS where chain_name='CHA0205_1';

 

JOB_NAME   CHAIN_NAME                     STATE                STEP_NAME

---------- ------------------------------ -------------------- ------------------------------

J0205_7    CHA0205_1                      NOT_STARTED          ST0205_3

J0205_7    CHA0205_1                      NOT_STARTED          ST0205_4

J0205_7    CHA0205_1                      NOT_STARTED          ST0205_1

J0205_7    CHA0205_1                      NOT_STARTED          ST0205_2

 

                                           

---两张测试表均为空

SQL> select * from t0205_2;

 

no rows selected

 

SQL> select * from t0205_34;

 

no rows selected

 

 

---下面我们手动运行J0205_0

exec dbms_Scheduler.run_job('J0205_0',FALSE);

 

---dba_scheduler_jobs看到j0205_7的上次运行时间变为了08-FEB-15 08.56.36

col last_start_date format a40

col next_run_date format a40

col job_name format a10

col job_subname format a10

col operation format a10

col state format a10

set linesize 150

select job_name,job_subname,last_start_date,next_run_date,enabled,state from dba_scheduler_jobs where job_name='J0205_7';

JOB_NAME   JOB_SUBNAM LAST_START_DATE                          NEXT_RUN_DATE                            ENABLED    STATE

---------- ---------- ---------------------------------------- ---------------------------------------- ---------- ----------

J0205_7    ST0205_1                                                                                     TRUE       SCHEDULED

J0205_7               08-FEB-15 08.56.36.667627 AM +08:00      08-FEB-15 08.53.11.000000 AM +08:00      TRUE       RUNNING

 

---DBA_SCHEDULER_RUNNING_CHAINS里内容为空说明J0205_7前一次的执行已经结束后一次的调度尚未开启,即当前没有处于运行状态显示除了ST0205_4没有被执行,其它三个step都成功执行过了

SQL> select job_name,chain_name,state,step_name,start_date,end_Date from DBA_SCHEDULER_RUNNING_CHAINS where chain_name='CHA0205_1';

 

no rows selected

 

---dba_scheduler_job_run_details有三个step成功执行以及整个chain成功完成的记录

SQL> select log_date,job_name,job_subname,status from dba_scheduler_job_run_details where job_name='J0205_7' order by log_date desc;

 

LOG_DATE                                                                    JOB_NAME   JOB_SUBNAM STATUS

--------------------------------------------------------------------------- ---------- ---------- ----------

08-FEB-15 08.56.36.575874 AM +08:00                                         J0205_7               SUCCEEDED

08-FEB-15 08.56.36.468056 AM +08:00                                         J0205_7    ST0205_3   SUCCEEDED

08-FEB-15 08.56.36.295616 AM +08:00                                         J0205_7    ST0205_2   SUCCEEDED

08-FEB-15 08.56.36.090806 AM +08:00                                         J0205_7    ST0205_1   SUCCEEDED

 

---t0205_34表里的数据表明确实是由ST0205_3插入的

col c1 format a20

col c2 format a20

col tm format a50

set linesize 100

select * from t0205_2;

 

C1                   C2                   TM

-------------------- -------------------- --------------------------------------------------

J0205_7              ST0205_2             08-FEB-15 08.56.36.292664 AM

 

select * from t0205_34;

C1                   C2                   TM

-------------------- -------------------- --------------------------------------------------

J0205_7              ST0205_3             08-FEB-15 08.56.36.465205 AM

 

---等待J0205_7下一分钟调度发起,CHA0205_1处于运行态,ST0205_1~ST0205_4均处于not_started状态,ST0205_1正在等待event的发生

select job_name,chain_name,state,step_name from DBA_SCHEDULER_RUNNING_CHAINS where chain_name='CHA0205_1';

 

JOB_NAME   CHAIN_NAME                     STATE                STEP_NAME

---------- ------------------------------ -------------------- ------------------------------

J0205_7    CHA0205_1                      NOT_STARTED          ST0205_3

J0205_7    CHA0205_1                      NOT_STARTED          ST0205_4

J0205_7    CHA0205_1                      NOT_STARTED          ST0205_1

J0205_7    CHA0205_1                      NOT_STARTED          ST0205_2

 

col job_name format a10

col job_subname format a10

col enabled format a10

col state format a10

col EVENT_QUEUE_NAME   format a20

col EVENT_QUEUE_owner  format a20

col EVENT_QUEUE_agent  format a20

col event_condition    format a20

set linesize 180

select job_name,job_subname,enabled,state,EVENT_QUEUE_NAME,EVENT_QUEUE_owner,EVENT_QUEUE_agent,event_condition from dba_scheduler_jobs where job_name='J0205_7' and job_subname='ST0205_1';

JOB_NAME   JOB_SUBNAM ENABLED    STATE      EVENT_QUEUE_NAME     EVENT_QUEUE_OWNER    EVENT_QUEUE_AGENT    EVENT_CONDITION

---------- ---------- ---------- ---------- -------------------- -------------------- -------------------- --------------------

J0205_7    ST0205_1   TRUE       SCHEDULED  SCHEDULER$_EVENT_QUE SYS                  AD_AGENT             tab.user_data.event_

                                            UE                                                             type='JOB_SUCCEEDED'

                                                                                                            and tab.user_data.O

                                                                                                           BJECT_NAME='J0205_0'

 

---我们再执行一次J0205_0,便会再一次触发J0205_7的执行

exec dbms_Scheduler.run_job('J0205_0',FALSE);

 

---08-FEB-15 09.08.52 J0205_7再次运行

SQL> select log_date,job_name,job_subname,status from dba_scheduler_job_run_details where job_name='J0205_7' order by log_date desc;

 

LOG_DATE                                                                    JOB_NAME   JOB_SUBNAM STATUS

--------------------------------------------------------------------------- ---------- ---------- ------------------------------

08-FEB-15 09.08.52.518654 AM +08:00                                         J0205_7               SUCCEEDED

08-FEB-15 09.08.52.422641 AM +08:00                                         J0205_7    ST0205_3   SUCCEEDED

08-FEB-15 09.08.52.262758 AM +08:00                                         J0205_7    ST0205_2   SUCCEEDED

08-FEB-15 09.08.52.067413 AM +08:00                                         J0205_7    ST0205_1   SUCCEEDED

08-FEB-15 08.56.36.575874 AM +08:00                                         J0205_7               SUCCEEDED

08-FEB-15 08.56.36.468056 AM +08:00                                         J0205_7    ST0205_3   SUCCEEDED

08-FEB-15 08.56.36.295616 AM +08:00                                         J0205_7    ST0205_2   SUCCEEDED

08-FEB-15 08.56.36.090806 AM +08:00                                         J0205_7    ST0205_1   SUCCEEDED

 

---next_run_Date显示J0205_7下次的执行时间将会是08-FEB-15 09.09.52,但不知何故last_start_date并没有更新为08-FEB-15 09.08.52

col last_start_date format a40

col next_run_date format a40

col job_name format a10

col job_subname format a10

col operation format a10

col state format a10

set linesize 150

select job_name,job_subname,last_start_date,next_run_date,enabled,state from dba_scheduler_jobs where job_name='J0205_7';

 

JOB_NAME   JOB_SUBNAM LAST_START_DATE                          NEXT_RUN_DATE                            ENABLED    STATE

---------- ---------- ---------------------------------------- ---------------------------------------- ---------- ----------

J0205_7               08-FEB-15 08.56.36.667627 AM +08:00      08-FEB-15 09.09.52.515966 AM +08:00      TRUE       SCHEDULED

 

---08-FEB-15 09.08.52这波运行完之后,相隔1分钟09.09.52 J0205_7再次发起,因为之后没有执行J0205_0去触发,所以chain一直处于空跑状态

select log_date,job_name,job_subname,operation,status from dba_scheduler_job_log where job_name='J0205_7' order by log_date desc;

 

LOG_DATE                                                                    JOB_NAME   JOB_SUBNAM OPERATION     STATUS

--------------------------------------------------------------------------- ---------- ---------- ------------- ------------------------------

08-FEB-15 09.09.52.535169 AM +08:00                                         J0205_7               CHAIN_START   RUNNING

08-FEB-15 09.08.52.518294 AM +08:00                                         J0205_7               CHAIN_RUN     SUCCEEDED

08-FEB-15 09.08.52.422237 AM +08:00                                         J0205_7    ST0205_3   RUN           SUCCEEDED

08-FEB-15 09.08.52.262197 AM +08:00                                         J0205_7    ST0205_2   RUN           SUCCEEDED

08-FEB-15 09.08.52.067038 AM +08:00                                         J0205_7    ST0205_1   RUN           SUCCEEDED

08-FEB-15 08.56.36.669587 AM +08:00                                         J0205_7               CHAIN_START   RUNNING

08-FEB-15 08.56.36.575523 AM +08:00                                         J0205_7               CHAIN_RUN     SUCCEEDED

08-FEB-15 08.56.36.467629 AM +08:00                                         J0205_7    ST0205_3   RUN           SUCCEEDED

08-FEB-15 08.56.36.295235 AM +08:00                                         J0205_7    ST0205_2   RUN           SUCCEEDED

08-FEB-15 08.56.36.090405 AM +08:00                                         J0205_7    ST0205_1   RUN           SUCCEEDED

 

以上走的是ST0205_1->ST0205_2->ST0205_3这条路径,下面验证一下ST0205_1->ST0205_2->ST0205_4这条路径是否能走通,我们通过修改t0205_2表结构来实现:

 

---停止J0205_7

exec dbms_Scheduler.stop_job('J0205_7');

 

---为了不与之前的日志混淆,先清理一下J0205_7的日志,purge_log需要manage scheduler权限

sqlplus '/ as sysdba'

 

grant manage scheduler to ad;

 

sqlplus ad/Uiop246!

 

exec dbms_scheduler.purge_log(log_history=>0,which_log=>'JOB_AND_WINDOW_LOG',job_name=>'J0205_7');

 

select count(*) from dba_scheduler_job_log where job_name='J0205_7';

  COUNT(*)

----------

         0

        

select count(*) from dba_scheduler_job_run_details where job_name='J0205_7';

  COUNT(*)

----------

         0

 

---修改表结构

truncate table t0205_2;

 

truncate table t0205_34;

 

alter table t0205_2 modify (tm number);

 

---J0205_7新一轮发起之后,手动运行J0205_7

exec dbms_scheduler.run_job('J0205_7',FALSE);

 

col last_start_date format a40

col next_run_date format a40

col job_name format a10

col job_subname format a10

col operation format a10

col state format a10

set linesize 150

select job_name,job_subname,last_start_date,next_run_date,enabled,state from dba_scheduler_jobs where job_name='J0205_7';

JOB_NAME   JOB_SUBNAM LAST_START_DATE                          NEXT_RUN_DATE                            ENABLED    STATE

---------- ---------- ---------------------------------------- ---------------------------------------- ---------- ----------

J0205_7    ST0205_1                                                                                     TRUE       SCHEDULED

J0205_7               08-FEB-15 09.51.55.722624 AM +08:00      08-FEB-15 09.51.55.707219 AM +08:00      TRUE       RUNNING

 

---运行J0205_0

exec dbms_scheduler.run_job('J0205_0');

 

---观察dba_scheduler_jobs

col job_name format a10

col job_subname format a10

col enabled format a10

col state format a10

col EVENT_QUEUE_NAME   format a20

col EVENT_QUEUE_owner  format a20

col EVENT_QUEUE_agent  format a20

col event_condition    format a20

set linesize 180

select job_name,job_subname,enabled,state,EVENT_QUEUE_NAME,EVENT_QUEUE_owner,EVENT_QUEUE_agent,event_condition from dba_scheduler_jobs where job_name='J0205_7' and job_subname='ST0205_1';

 

no rows selected

 

---dba_scheduler_job_run_details内容推断出当前执行的是ST0205_1->ST0205_2->ST0205_4

SQL> select log_date,job_name,job_subname,status from dba_scheduler_job_run_details where job_name='J0205_7' order by log_date desc;

 

LOG_DATE                                                                    JOB_NAME   JOB_SUBNAM STATUS

--------------------------------------------------------------------------- ---------- ---------- ------------------------------

08-FEB-15 09.54.04.544999 AM +08:00                                         J0205_7               SUCCEEDED

08-FEB-15 09.54.04.450601 AM +08:00                                         J0205_7    ST0205_4   SUCCEEDED

08-FEB-15 09.54.04.361981 AM +08:00                                         J0205_7    ST0205_2   FAILED

08-FEB-15 09.54.04.081797 AM +08:00                                         J0205_7    ST0205_1   SUCCEEDED

08-FEB-15 09.50.55.709786 AM +08:00                                         J0205_7               STOPPED

 

---ST0205_2 failed的原因是t0205_2表结构变更引起的PRC0205_2过程失效

col additional_info format a50

set linesize 140

select job_name,job_subname,status,additional_info from dba_scheduler_job_run_details where job_name='J0205_7' and job_subname='ST0205_2' order by log_date desc;

JOB_NAME   JOB_SUBNAM STATUS                         ADDITIONAL_INFO

---------- ---------- ------------------------------ --------------------------------------------------

J0205_7    ST0205_2   FAILED                         CHAIN_LOG_ID="62291", STEP_NAME="ST0205_2", ORA-06

                                                     575: Package or function PRC0205_2 is in an invali

                                                     d state

 

---t0205_34也验证了该条记录是有ST0205_4插入的

col c1 format a20

col c2 format a20

col tm format a50

set linesize 100

SQL> select * from t0205_2;

 

no rows selected

 

SQL>

SQL> select * from t0205_34;

 

C1                   C2                   TM

-------------------- -------------------- --------------------------------------------------

J0205_7              ST0205_4             08-FEB-15 09.54.04.447778 AM

 

 

 

alter table t0205_2

exec dbms_scheduler.set_attribute('J0205_7','start_date',systimestamp+1/1440);

exec dbms_scheduler.set_attribute('J0205_7','end_date',systimestamp+10/1440);

 

exec dbms_Scheduler.stop_job('J0205_7');

exec dbms_scheduler.drop_job('J0205_7');

exec dbms_scheduler.drop_job('J0205_0');

exec dbms_scheduler.drop_chain('cha0205_1');

exec dbms_scheduler.drop_program('P0205_2,P0205_34');

exec dbms_scheduler.drop_schedule('est0205_1');

 

truncate table t0205_2;

truncate table t0205_34;

///////////////////////////

// 11、创建一个event-based job

///////////////////////////

两个jobJ0205_3J0205_4,其中J0205_3启动后及完成后均会自动触发J0205_4运行,J0205_3启动和完成阶段产生的事件消息会进入名为SYS.SCHEDULER$_EVENT_QUEUE的队列,该队列专门用于存放scheduler运行期间产生的event messageJ0205_4owner必须有权限从这个队列中把和自己有关的event message Dequeue出来如果判断是J0205_3发来的则触发J0205_4开始运行

 

---创建测试表

create table t0205_3 (c1 varchar2(10),tm timestamp,event_info varchar2(500));

 

---创建J0205_3运行所需的procedureprogram

create or replace procedure PRC0205_3(v_jname in varchar2,v_jstart in timestamp)

as

begin

insert into t0205_3(c1,tm,event_info) values(v_jname,v_jstart,v_jname||' start');

dbms_lock.sleep(30);

insert into t0205_3(c1,tm,event_info) values(v_jname,systimestamp,v_jname||' end');

commit;

end;

/

 

exec dbms_scheduler.create_program(program_name=>'P0205_3',program_type=>'STORED_PROCEDURE',program_action=>'PRC0205_3',number_of_arguments=>2);

 

--定义program参数

exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_3',metadata_attribute=>'job_name',argument_position=>1);

exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_3',metadata_attribute=>'job_start',argument_position=>2);

 

--创建J0205_3,并指定其开始及成功结束后均生成event

exec dbms_scheduler.create_job(job_name=>'J0205_3',program_name=>'P0205_3',start_date=>systimestamp+1/1440,repeat_interval=>'FREQ=MINUTELY',end_date=>systimestamp+10/1440,auto_drop=>FALSE);

exec dbms_scheduler.set_attribute('J0205_3','raise_events',DBMS_SCHEDULER.JOB_STARTED+DBMS_SCHEDULER.JOB_SUCCEEDED);

 

--赋予ad用户访问SYS.SCHEDULER$_EVENT_QUEUE队列的权限,创建agentagent名称为ad_agent(两种方法二选一)

***SYS用户执行

exec DBMS_AQADM.ENABLE_DB_ACCESS('ad_agent','ad');

***也可以使用scott用户执行

exec DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER('ad_agent');

 

--创建基于eventschedule,这个schedule的触发条件是:事件队列SYS.SCHEDULER$_EVENT_QUEUE里名为ad_agent收到J0205_3发送来的Event message

exec dbms_scheduler.create_event_schedule('SC0205_3',event_condition=>'tab.user_data.object_name=''J0205_3''',queue_spec=>'SYS.SCHEDULER$_EVENT_QUEUE,ad_agent');

 

--创建J0205_4运行所需的procedureprogram,将SYS.SCHEDULER$_EVENT_QUEUE的信息输出至v_message变量并存入到t0205_3表的event_info字段

create or replace procedure PRC0205_4(v_jname in varchar2,v_jstart in timestamp,v_event_info in SYS.SCHEDULER$_EVENT_INFO)

as

v_message varchar2(1024);

begin

v_message:='EVENT_TYPE:'||v_event_info.event_type||';;'||'OBJECT_NAME:'||v_event_info.object_name||';;'||'EVENT_TIMESTAMP:'||to_char(v_event_info.EVENT_TIMESTAMP,'yyyymmdd hh24:mi:ss.ff')||';;'||'EVENT_STATUS:'||v_event_info.EVENT_STATUS||';;'||'ERROR_CODE:'||v_event_info.error_code;

insert into t0205_3(c1,tm,event_info) values(v_jname,v_jstart,v_message);

commit;

end;

/

 

exec dbms_scheduler.create_program(program_name=>'P0205_4',program_type=>'STORED_PROCEDURE',program_action=>'PRC0205_4',number_of_arguments=>3);

 

--定义program参数

exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_4',metadata_attribute=>'job_name',argument_position=>1);

exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_4',metadata_attribute=>'job_start',argument_position=>2);

exec dbms_scheduler.define_metadata_argument(program_name=>'P0205_4',metadata_attribute=>'event_message',argument_position=>3);

 

--创建J0205_4,并Enable所有programjob

exec dbms_scheduler.create_job(job_name=>'J0205_4',program_name=>'P0205_4',schedule_name=>'SC0205_3',auto_drop=>FALSE);

exec dbms_scheduler.enable('P0205_3,P0205_4,J0205_3,J0205_4');

 

--检查t0205_3表,J0205_4所在行的event_info列显示了当时触发J0205_4运行的事件消息内容

SQL> select * from t0205_3 order by tm;

C1         TM                                  EVENT_INFO

---------- ----------------------------------- ------------------------------------------------------------------------------------------------------------------------

J0205_3    05-FEB-15 03.42.08.080748 PM        J0205_3 start

J0205_4    05-FEB-15 03.42.09.007749 PM        EVENT_TYPE:JOB_STARTED;;OBJECT_NAME:J0205_3;;EVENT_TIMESTAMP:20150205 15:42:08.080655000;;EVENT_STATUS:1;;ERROR_CODE:0

J0205_3    05-FEB-15 03.42.38.121844 PM        J0205_3 end

J0205_4    05-FEB-15 03.42.39.005193 PM        EVENT_TYPE:JOB_SUCCEEDED;;OBJECT_NAME:J0205_3;;EVENT_TIMESTAMP:20150205 15:42:38.122522000;;EVENT_STATUS:0;;ERROR_CODE:0

J0205_3    05-FEB-15 03.43.08.105371 PM        J0205_3 start

J0205_4    05-FEB-15 03.43.09.007843 PM        EVENT_TYPE:JOB_STARTED;;OBJECT_NAME:J0205_3;;EVENT_TIMESTAMP:20150205 15:43:08.105292000;;EVENT_STATUS:1;;ERROR_CODE:0

J0205_3    05-FEB-15 03.43.38.112703 PM        J0205_3 end

J0205_4    05-FEB-15 03.43.39.003996 PM        EVENT_TYPE:JOB_SUCCEEDED;;OBJECT_NAME:J0205_3;;EVENT_TIMESTAMP:20150205 15:43:38.113498000;;EVENT_STATUS:0;;ERROR_CODE:0

 

附,上面用到的事件队列消息类型SYS.SCHEDULER$_EVENT_INFO结构如下

SQL> desc SYS.SCHEDULER$_EVENT_INFO

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EVENT_TYPE                                         VARCHAR2(4000)

 OBJECT_OWNER                                       VARCHAR2(4000)

 OBJECT_NAME                                        VARCHAR2(4000)

 EVENT_TIMESTAMP                                    TIMESTAMP(6) WITH TIME ZONE

 ERROR_CODE                                         NUMBER

 ERROR_MSG                                          VARCHAR2(4000)

 EVENT_STATUS                                       NUMBER

 LOG_ID                                             NUMBER

 RUN_COUNT                                          NUMBER

 FAILURE_COUNT                                      NUMBER

 RETRY_COUNT                                        NUMBER

 SPARE1                                             NUMBER

 SPARE2                                             NUMBER

 SPARE3                                             VARCHAR2(4000)

 SPARE4                                             VARCHAR2(4000)

 SPARE5                                             TIMESTAMP(6) WITH TIME ZONE

 SPARE6                                             TIMESTAMP(6) WITH TIME ZONE

 SPARE7                                             RAW(2000)

 SPARE8                                             RAW(2000)

 OBJECT_SUBNAME                                     VARCHAR2(4000)

 JOB_CLASS_NAME                                     VARCHAR2(4000)

 

///////////////////////////

// 12、创建一个File arrival event based job

///////////////////////////

/home/oracle目录下存入新的以fwatch开头的文件,且文件的size > 1字节时,触发名为J0208_3job运行,J0208_3的功能就是将接收到的文件信息进行输出

--赋予ad用户修改FILE_WATCHER_SCHEDULE的权限

sqlplus '/as sysdba'

 

grant alter on SYS.FILE_WATCHER_SCHEDULE to ad;

 

--创建测试表

drop table t0208_3;

 

create table t0208_3 (msg varchar2(2000),tm timestamp);

 

--创建procedure,用于输出消息队列里存放的文件信息

create or replace procedure PRC0208_3 (v_file_result in SYS.SCHEDULER_FILEWATCHER_RESULT)

as

v_out_msg varchar2(2000);

begin

v_out_msg:='destination:'||v_file_result.destination||'---'||'directory_path:'||v_file_result.directory_path||'/'||v_file_result.actual_file_name||'---'||'file_size:'||v_file_result.file_size||'---'||'found_time:'||to_char(v_file_result.file_timestamp,'yyyymmdd hh24:mi:ss.ff TZH:TZM');

insert into t0208_3 values(v_out_msg,systimestamp);

commit;

end;

/

 

--创建credentialfile_watcherprogram

exec dbms_scheduler.create_credential(credential_name=>'C0208_3',username=>'oracle',password=>'uiop7890');

 

exec dbms_scheduler.create_file_Watcher(file_watcher_name=>'FW0208_3',directory_path=>'/home/oracle',file_name=>'fwatch*',credential_name=>'c0208_3',enabled=>FALSE);

 

exec dbms_scheduler.create_program(program_name=>'P0208_3',program_type=>'STORED_PROCEDURE',program_action=>'PRC0208_3',number_of_arguments=>1);

 

exec dbms_scheduler.define_metadata_argument(program_name=>'P0208_3',metadata_attribute=>'event_message',argument_position=>1);

 

--创建job,event_condition=>'tab.user_data.file_size > 1表示只有size>1字节的文件才会触发job执行

exec dbms_scheduler.create_job(job_name=>'J0208_3',program_name=>'P0208_3',event_condition=>'tab.user_data.file_size > 1',queue_spec=>'FW0208_3',end_Date=>systimestamp+300/1440,auto_Drop=>FALSE);

 

--修改FILE_WATCHER_SCHEDULE监测的时间为每分钟一次

exec dbms_scheduler.set_attribute('SYS.FILE_WATCHER_SCHEDULE','repeat_interval','FREQ=MINUTELY');

 

--enable all objects

exec dbms_Scheduler.enable('P0208_3,FW0208_3,J0208_3');

 

--手工在/home/oracle/下创建文件fwatch1.txt

oracle@jq570322b:/home/oracle>ls -l fwatch1.txt

-rwxr-xr-x    1 oracle   oinstall       1438 Feb 08 17:55 fwatch1.txt

 

--1分钟后,查看队列表sys.aq$scheduler_filewatcher_qt,可以看出对于fwatch1.txt文件,对应两条记录,一条状态为READY是进入队列的记录,状态为PROCESSED的是出队列记录

set pause on

set linesize 180

col enq_time format a9

col deq_time format a9

col msg_state format a10

col user_data format a90

col consumer_name format a10

select enq_time,deq_time,msg_state,consumer_name,user_data from sys.aq$scheduler_filewatcher_qt order by enq_time desc;

ENQ_TIME  DEQ_TIME  MSG_STATE  CONSUMER_N USER_DATA(DESTINATION, DIRECTORY_PATH, ACTUAL_FILE_NAME, FILE_SIZE, FILE_TIMESTAMP, TS_MS_

--------- --------- ---------- ---------- ------------------------------------------------------------------------------------------

20150208  20150208  PROCESSED  SCHEDULER$ SCHEDULER_FILEWATCHER_RESULT('jq570322b', '/home/oracle', 'fwatch1.txt', 1438, '08-FEB-15

17:57:01  17:57:01             _EVENT_AGE 09.55.53.000000 AM +00:00', 1.4234E+12, SCHEDULER_FILEWATCHER_REQ_LIST(SCHEDULER_FILEWATCH

                               NT         ER_REQUEST('AD', 'FW0208_3', '/home/oracle', 'fwatch*', 'AD', 'C0208_3', 0, 30000)))

 

20150208            READY      SCHED$_AGT SCHEDULER_FILEWATCHER_RESULT('jq570322b', '/home/oracle', 'fwatch1.txt', 1438, '08-FEB-15

17:57:01                       $_53       09.55.53.000000 AM +00:00', 1.4234E+12, SCHEDULER_FILEWATCHER_REQ_LIST(SCHEDULER_FILEWATCH

                                          ER_REQUEST('AD', 'FW0208_3', '/home/oracle', 'fwatch*', 'AD', 'C0208_3', 0, 30000)))

 

--sys.SCHEDULER_FILEWATCHER_RESULT object里的file_timestamp是以GMT时间显示的,所以msg里看到的时间会相差8小时,其表示的是文件创建的时间

col msg format a90

col tm format a30

set linesize 150

select * from t0208_3;

MSG                                                                                        TM

------------------------------------------------------------------------------------------ ------------------------------

destination:jq570322b---directory_path:/home/oracle/fwatch1.txt---file_size:1438---found_time 08-FEB-15 05.57.02.028360 PM

:20150208 09:55:53.000000000 +00:00

 

--手工在/home/oracle/下创建文件fwatch2.txt,大小为1字节

oracle@jq570322b:/home/oracle>ls -l fwatch2.txt

-rw-r--r--    1 oracle   oinstall          1 Feb 08 17:57 fwatch2.txt

 

--过了1分钟后sys.aq$scheduler_filewatcher_qt对应fwatch2.txt多了一条Ready的记录,但并没有PROCESSED的记录,这是因为fwatch2.txtfile_size不满足>1的条件所以不予以处理

select enq_time,deq_time,msg_state,consumer_name,user_data from sys.aq$scheduler_filewatcher_qt order by enq_time desc

 

 

ENQ_TIME  DEQ_TIME  MSG_STATE  CONSUMER_N USER_DATA(DESTINATION, DIRECTORY_PATH, ACTUAL_FILE_NAME, FILE_SIZE, FILE_TIMESTAMP, TS_MS_

--------- --------- ---------- ---------- ------------------------------------------------------------------------------------------

20150208            READY      SCHED$_AGT SCHEDULER_FILEWATCHER_RESULT('jq570322b', '/home/oracle', 'fwatch2.txt', 1, '08-FEB-15 09.

17:59:31                       $_53       57.39.000000 AM +00:00', 1.4234E+12, SCHEDULER_FILEWATCHER_REQ_LIST(SCHEDULER_FILEWATCHER_

                                          REQUEST('AD', 'FW0208_3', '/home/oracle', 'fwatch*', 'AD', 'C0208_3', 0, 30000)))

 

20150208  20150208  PROCESSED  SCHEDULER$ SCHEDULER_FILEWATCHER_RESULT('jq570322b', '/home/oracle', 'fwatch1.txt', 1438, '08-FEB-15

17:57:01  17:57:01             _EVENT_AGE 09.55.53.000000 AM +00:00', 1.4234E+12, SCHEDULER_FILEWATCHER_REQ_LIST(SCHEDULER_FILEWATCH

                               NT         ER_REQUEST('AD', 'FW0208_3', '/home/oracle', 'fwatch*', 'AD', 'C0208_3', 0, 30000)))

 

20150208            READY      SCHED$_AGT SCHEDULER_FILEWATCHER_RESULT('jq570322b', '/home/oracle', 'fwatch1.txt', 1438, '08-FEB-15

17:57:01                       $_53       09.55.53.000000 AM +00:00', 1.4234E+12, SCHEDULER_FILEWATCHER_REQ_LIST(SCHEDULER_FILEWATCH

                                          ER_REQUEST('AD', 'FW0208_3', '/home/oracle', 'fwatch*', 'AD', 'C0208_3', 0, 30000)))

 

--t0208_3表里的记录没有变化

col msg format a90

col tm format a30

set linesize 150

select * from t0208_3;

MSG                                                                                        TM

------------------------------------------------------------------------------------------ ------------------------------

destination:jq570322b---directory_path:/home/oracle/fwatch1.txt---file_size:1438---found_time 08-FEB-15 05.57.02.028360 PM

:20150208 09:55:53.000000000 +00:00

 

 

TYPE scheduler_filewatcher_result IS OBJECT (

  destination         VARCHAR2(4000),

  directory_path      VARCHAR2(4000),

  actual_file_name    VARCHAR2(4000),

  file_size           NUMBER,

  file_timestamp      TIMESTAMP WITH TIME ZONE,

  ts_ms_from_epoch    NUMBER,

  matching_requests   SYS.SCHEDULER_FILEWATCHER_REQ_LIST);

 

 

#exec dbms_scheduler.set_attribute('S0208_3','event_spec','tab.user_data.file_size>1','FW0208_3');

#

#exec dbms_scheduler.stop_job('J0208_3');

#exec dbms_scheduler.drop_job('J0208_3');

#exec dbms_scheduler.drop_file_watcher('FW0208_3');

#exec dbms_scheduler.drop_program('P0208_3');

#exec dbms_scheduler.drop_schedule('S0208_3');

#

#exec dbms_scheduler.purge_log(job_name=>'J0208_3');

#exec dbms_scheduler.drop_file_watcher('FW0208_3');

 

///////////////

// 13、创建一个chain类型的job,使用evaluation_interval参数

///////////////

evaluation_interval参数的实用之处在于能够定时去判定chain定义之外的条件是否满足,如果满足则开始运行特定的step,例如下面的例子完成的任务是:当ST0210_6步骤运行成功且trigtab表不为空,则开始运行ST0210_5这个步骤

drop table t0210_5;

drop table trigtab;

exec dbms_scheduler.stop_job('J0210_5');

exec dbms_scheduler.drop_job('J0210_5');

exec dbms_Scheduler.drop_chain('cha0210_5');

exec dbms_scheduler.drop_program('P0210_5,P0210_6');

 

--create table and procedure for test

create table t0210_5(c1 varchar2(10),tm timestamp);

 

create table trigtab(id number);

 

create or replace procedure PRC0210_5

as

begin

insert into t0210_5 values('detected',systimestamp);

commit;

end;

/

 

create or replace procedure PRC0210_6

as

begin

dbms_output.put_line('AAA');

end;

/

 

--create programchainstep

exec dbms_scheduler.create_chain(chain_name=>'cha0210_5',rule_set_name=>NULL,evaluation_interval=>interval '1' minute);

exec dbms_scheduler.create_program(program_name=>'P0210_5',program_type=>'STORED_PROCEDURE',program_action=>'PRC0210_5');

exec dbms_scheduler.create_program(program_name=>'P0210_6',program_type=>'STORED_PROCEDURE',program_action=>'PRC0210_6');

exec dbms_scheduler.define_chain_step(chain_name=>'cha0210_5',step_name=>'ST0210_5',program_name=>'P0210_5');

exec dbms_scheduler.define_chain_step(chain_name=>'cha0210_5',step_name=>'ST0210_6',program_name=>'P0210_6');

 

--define chain rule R0210_61 to start the chain from step ST0210_6

exec dbms_scheduler.define_chain_rule(chain_name=>'cha0210_5',condition=>'TRUE',action=>'start ST0210_6',rule_name=>'R0210_61');

 

--define chain rule R0210_62 to start ST0210_5 if ST0210_6 is succeeded and rowcount in trigtab is greater than 0 and Error occurs here

exec dbms_scheduler.define_chain_rule(chain_name=>'cha0210_5',condition=>':ST0210_6.state=''SUCCEEDED'' and (select count(*) from trigtab) > 0',action=>'start ST0210_5',rule_name=>'R0210_62');

 

exec dbms_scheduler.define_chain_rule(chain_name=>'cha0210_5',condition=>':ST0210_5.state=''SUCCEEDED''',action=>'END',rule_name=>'R0210_5');

 

--create a job and enable all scheduler objects

exec dbms_scheduler.create_job(job_name=>'J0210_5',job_type=>'CHAIN',job_action=>'cha0210_5',end_date=>sysdate+100/1440,repeat_interval=>'FREQ=MINUTELY;INTERVAL=5');

exec dbms_scheduler.enable('cha0210_5,P0210_5,P0210_6,J0210_5');

 

---验证结果

select * from t0210_5;

 

no rows selected

 

select * from trigtab;

 

no rows selected

 

insert into trigtab values(1);

commit;

 

--过了1分钟后,立马有数据进来,就是因为设置了evaluation_interval之后,才能这么及时

select * from t0210_5;

C1         TM

---------- ------------------------------

detected   12-FEB-15 09.24.45.116423 AM

 

truncate Table trigtab;

 

--如果将evaluation_interval设置为NULL,意味着不会定时去检测这些外部条件,且即使到了job的发起时间也不会检测,我们来看一下

exec dbms_scheduler.stop_job('J0210_5');

exec dbms_scheduler.set_attribute_null('cha0210_5','evaluation_interval');

 

---修改完属性后J0210_509.51.45开始运行

select * from dba_scheduler_job_log where job_name='J0210_5' order by log_date desc;

LOG_ID    LOG_DATE        OWNER    JOB_NAME      JOB_SUBNAME        JOB_CLASS       OPERATION      STATUS

71422       12-FEB-15 09.51.45.108690 AM +08:00 AD    J0210_5   ST0210_6          DEFAULT_JOB_CLASS       RUN SUCCEEDED

71421       12-FEB-15 09.51.45.012109 AM +08:00 AD    J0210_5            DEFAULT_JOB_CLASS       CHAIN_START RUNNING

71417       12-FEB-15 09.50.23.868297 AM +08:00 AD    J0210_5                      CHAIN_RUN    STOPPED

 

---由于trigtab里没有数据所以t0210_5的数据没有更新

SQL> select count(*) from trigtab;

 

  COUNT(*)

----------

         0

        

SQL> select * from t0210_5;

 

C1         TM

---------- ----------------------------------------

detected   12-FEB-15 09.24.45.116423 AM

detected   12-FEB-15 09.26.45.208953 AM

detected   12-FEB-15 09.37.45.111696 AM

 

SQL> insert into trigtab values(2);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select sysdate from dual;

 

SYSDATE

-----------------

20150212 09:52:17

 

---因为是每隔5分钟运行一次,J0210_5下一次的运行时间将会是12-FEB-15 09.56.45,但到了20150212 09:57仍然没有数据进入到t0210_5

SQL> select sysdate from dual;

 

SYSDATE

-----------------

20150212 09:57:40

 

select * from t0210_5;

C1         TM

---------- ----------------------------------------

detected   12-FEB-15 09.24.45.116423 AM

detected   12-FEB-15 09.26.45.208953 AM

detected   12-FEB-15 09.37.45.111696 AM

 

--原因是对于step以外的步骤作为condition的,这里指的是(select count(*) from trigtab) > 0',必须要使用evaluation_interval,这是admin guide里的一段原话useful to start chain steps based on time of day or based on occurrences external to the chain,重新把evaluation_interval设上

exec dbms_scheduler.set_attribute('cha0210_5','evaluation_interval',interval '1' minute);

exec dbms_scheduler.stop_job('J0210_5');

 

SQL> insert into trigtab values(1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select sysdate from dual

  2  ;

 

SYSDATE

-----------------

20150212 10:22:12

 

SQL>  select last_start_date from dba_scheduler_jobs where job_name='J0210_5';

 

LAST_START_DATE

---------------------------------------------------------------------------

12-FEB-15 10.21.45.010595 AM +08:00

 

---由于J0210_5上一次的运行时间在10.21.45,预计新插入记录的时间戳为10.22.45,查询结果验证了我们的判断

SQL> select * from t0210_5;

 

C1         TM

---------- ----------------------------------------

detected   12-FEB-15 10.22.45.111776 AM

detected   12-FEB-15 09.24.45.116423 AM

detected   12-FEB-15 09.26.45.208953 AM

detected   12-FEB-15 09.37.45.111696 AM

 

///////////////////////////////////////////////////////////////////////////////////////////////

/// 14、创建remote external job

///////////////////////////////////////////////////////////////////////////////////////////////

前提是要在运行remote external jobremote主机上安装好schedule agent并完成注册,具体可参照。。。。。????,jq570321aremote hostjq570322bjob所在的数据库服务器

 

---remote host JQ570321A上创建Shell脚本

vi /home/sagent/oracle1.sh;chmod u+x /home/sagent/oracle1.sh

#!/usr/bin/ksh

export TZ=BEIST-8

date > /home/sagent/oracle1.log

 

脚本里必须包含的内容是shell解释器、必要的用户环境变量,比如上述内容中如果没有export TZ=BEIST-8这行,那么输出结果中的时间将是UTC时间

 

--remote host JQ570321A/etc/hosts里添加即将job所在的数据库服务器ip地址和主机名对应关系,这一步很重要决定了jobremote host上执行完毕后能否将结果回传到数据库服务器,如果不添加这个对应关系会发现这个job始终处于running状态,在agent.log里会出现Submitting results failed with error: java.net.UnknownHostException: jq570322b的错误

echo "10.10.141.209   jq570322b" >> /etc/hosts

 

---SYS用户赋权

sqlplus '/as sysdba'

grant create external job to scott;

grant create job to scott;

 

---Scott用户创建programcredentialjob等对象,destination_nameSchedule agent向数据库注册时获得的名称,一般都是主机名,可从dba_SCHEDULER_EXTERNAL_DESTS.destination_name获得

sqlplus scott/773946

exec DBMS_SCHEDULER.drop_job(job_name=>'J0215_8',force=>TRUE);

exec DBMS_SCHEDULER.purge_log(job_name=>'J0215_8');

exec DBMS_SCHEDULER.drop_CREDENTIAL(credential_name=>'C0215_8');

exec DBMS_SCHEDULER.drop_program(program_name=>'P0215_8');

 

exec DBMS_SCHEDULER.create_program(program_name=>'P0215_8',program_type=>'EXECUTABLE',program_action=>'/home/sagent/oracle1.sh');

exec DBMS_SCHEDULER.CREATE_CREDENTIAL(credential_name=>'C0215_8',username=>'sagent',password=>'asdf3_14');

exec DBMS_SCHEDULER.create_job(job_name=>'J0215_8',program_name=>'P0215_8',start_Date=>sysdate+0.3/1440,end_date=>sysdate+60/1440,repeat_interval=>'FREQ=SECONDLY;INTERVAL=30',credential_name=>'C0215_8',destination_name=>'SYS.JQ570321A',auto_Drop=>FALSE);  <=这里destination_name别忘了前面加上sys.

 

exec DBMS_SCHEDULER.enable('P0215_8');

exec DBMS_SCHEDULER.enable('P0215_8,J0215_8');

 

---登陆remote host检查执行结果

sagent@jq570321a:/home/sagent>cat oracle1.log

BEIST-8

Mon Feb 16 07:14:46 BEIST 2015

 

sagent@jq570321a:/home/sagent>ls -rlt oracle1.log

-rw-r-----    1 sagent   dba              39 Feb 16 07:14 oracle1.log

 

---每次执行后会在remote主机的agent.log下留下调度记录,agent.log中日志记录的详细程度可以通过schagent.conf里的LOGGING_LEVEL进行指定

cd $ORACLE_HOME/scheduler/execution_agent/data

tail -n 2 agent.log

2015.02.16 07:14:46 GMT+08:00 Log ID: job_32832_128 Name: SCOTT.J0215_8 Source: jq570322b : 30415 Source DB: TSTDB1 Command: /home/sagent/oracle1.sh

2015.02.16 07:14:46 GMT+08:00 Job terminated successfully. Duration: 0.02 seconds

 

/////////////////////

// 15、修改job class所关联的consumer group以实现对该job_class下所有job进行优先级调整的目的

/////////////////////

假设名为jclass1job class里的所有job在白天运行优先级较高,到了晚上运行优先级较低,那么在白天可以将job class关联到拥有较多资源的资源组cgday,而到了晚上将job class关联到拥有较少资源的资源组cgnight

--创建资源计划resplan1等对象,以sysdba用户执行

exec dbms_resource_manager.create_pending_area;

exec dbms_resource_manager.create_plan(plan=>'resplan1',comment=>'resplan1');

exec dbms_resource_manager.create_consumer_group(consumer_group=>'cgday',comment=>'cgday');

exec dbms_resource_manager.create_consumer_group(consumer_group=>'cgnight',comment=>'cgnight');

exec dbms_resource_manager.create_plan_directive(plan=>'resplan1',group_or_subplan=>'cgday',mgmt_p1=>50,comment=>'dir_cgday');

exec dbms_resource_manager.create_plan_directive(plan=>'resplan1',group_or_subplan=>'cgnight',mgmt_p1=>20,comment=>'dir_cgnight');

exec dbms_resource_manager.create_plan_directive(plan=>'resplan1',group_or_subplan=>'OTHER_GROUPS',mgmt_p1=>30,comment=>'dir_other');

exec dbms_resource_manager.submit_pending_area;

 

exec DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(grantee_name=>'AD',consumer_group=>'cgday',grant_option=>FALSE);

exec DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(grantee_name=>'AD',consumer_group=>'cgnight',grant_option=>FALSE);

 

--赋给普通用户ad必要的权限,后面的操作若不加以说明则均以ad用户执行

grant manage scheduler to ad;

grant create job to ad;

 

--清理scheduler对象

sqlplus ad/Uiop246!

drop table tclass1;

select 'exec dbms_scheduler.close_window('''||window_name||''');',active from dba_scheduler_windows where active='TRUE'; --关闭所有openwindow

exec dbms_scheduler.drop_job(job_name=>'J131_4');

exec dbms_scheduler.drop_program_argument(program_name=>'p131_4',argument_position=>1);

exec dbms_scheduler.drop_program(program_name=>'p131_4');

exec dbms_scheduler.drop_job_class(job_class_name=>'jclass1');

exec dbms_scheduler.drop_window(window_name=>'w131_4');

exec dbms_scheduler.drop_schedule(schedule_name=>'S131_4');

 

--创建job_class,先关联cgday这个resource group,注意job_classwindow永远是创建在sys用户下的

exec dbms_scheduler.create_job_class(job_class_name=>'jclass1',resource_consumer_group=>'cgday');

 

col owner format a15

col object_name format a15

col object_type format a15

set linesize 100

select owner,object_name,object_type from dba_objects where object_name='JCLASS1';

 

OWNER           OBJECT_NAME     OBJECT_TYPE

--------------- --------------- ---------------

SYS             JCLASS1         JOB CLASS

 

--sys用户执行:赋权job class的执行权限给ad,否则后面在enable job或者run job的时候会收到ORA-27486: insufficient privileges

grant execute on jclass1 to ad;

 

--创建测试用表

create table tclass1 (jname varchar2(20),sid_serial varchar2(30),resource_cgname varchar2(10),tm timestamp);

 

--创建存储过程

create or replace procedure query_jobinfo(input_jobname in varchar2) as

v_sid_serial varchar2(30);

v_resource_cgname varchar2(10);

v_tm timestamp;

begin

select ''''||sid||'-'||serial#||'''',RESOURCE_CONSUMER_GROUP into v_sid_serial,v_resource_cgname from v$session where sid=sys_context('userenv','sid');

insert into tclass1 values(input_jobname,v_sid_serial,v_resource_cgname,systimestamp);

commit;

end;

/

 

--创建program

exec dbms_scheduler.create_program(program_name=>'p131_4',program_type=>'STORED_PROCEDURE',program_action=>'query_jobinfo',number_of_arguments=>1);

 

--定义传给program的参数

exec dbms_scheduler.define_metadata_argument(program_name=>'p131_4',metadata_attribute=>'job_name',argument_position=>1);

 

--创建schedule,从当前时间+1分钟开始,每分钟执行一次

exec dbms_scheduler.create_schedule(schedule_name=>'s131_4',start_date=>systimestamp+1/1440,repeat_interval=>'FREQ=MINUTELY',end_date=>systimestamp+100/1440);

 

--创建window,每个窗口持续时长为2分钟

exec dbms_scheduler.create_window(window_name=>'w131_4',resource_plan=>'resplan1',schedule_name=>'s131_4',duration=>interval '2' minute);

 

--创建job,默认情况下jobprogram创建出来都是disabled状态,joblog-level=OFF,但job_classlog-levelRUNS,这里以job_class的日志级别为准

exec dbms_scheduler.create_job(job_name=>'j131_4',program_name=>'p131_4',job_class=>'jclass1',schedule_name=>'sys.w131_4',auto_drop=>FALSE);

 

col job_name format a10

col job_Action format a10

col start_date format a15

col last_start_date format a15

col next_run_date format a15

col repeat_interval format a17

col end_Date format a15

col schedule_name format a20

set linesize 170

set pagesize 200

select job_name,job_Action,schedule_name,auto_drop,start_date,repeat_interval,end_Date,LAST_START_DATE,next_run_date,enabled,state,logging_level from dba_Scheduler_jobs where job_name in ('J131_4');

 

JOB_NAME   JOB_ACTION SCHEDULE_NAME        AUTO_ START_DATE      REPEAT_INTERVAL   END_DATE        LAST_START_DATE NEXT_RUN_DATE   ENABL STATE           LOGGING_LEV

---------- ---------- -------------------- ----- --------------- ----------------- --------------- --------------- --------------- ----- --------------- -----------

J131_4                W131_4               FALSE                                                                                   FALSE DISABLED        OFF

 

SQL> select logging_level from dba_scheduler_job_classes where job_class_name='JCLASS1';

 

LOGGING_LEV

-----------

RUNS

 

col program_action format a20

SQL> select owner,program_name,program_action,enabled from dba_scheduler_programs where program_name='P131_4'

 

OWNER           PROGRAM_NAME                   PROGRAM_ACTION       ENABL

--------------- ------------------------------ -------------------- -----

AD              P131_4                         query_jobinfo        FALSE

 

--enable jobprogram

exec dbms_scheduler.enable('p131_4,j131_4');   

 

--运行结果显示J131_4这个job运行期间确实归属于cgday这个资源组

col tm format a40

set linesize 150

select * from tclass1;

 

JNAME                SID_SERIAL                     RESOURCE_C TM

-------------------- ------------------------------ ---------- ---------------------------------------------------------------------------

J131_4               '6151-29923'                   CGDAY      01-FEB-15 01.31.20.180852 AM

J131_4               '6624-31309'                   CGDAY      01-FEB-15 01.26.16.292778 AM

J131_4               '6624-31317'                   CGDAY      01-FEB-15 01.28.20.182073 AM

 

--dba_scheduler_jobsenable属性从enable=false变为了enable=true

select job_name,job_Action,schedule_name,auto_drop,start_date,repeat_interval,end_Date,LAST_START_DATE,next_run_date,enabled,state,logging_level from dba_Scheduler_jobs where job_name in ('J131_4');

 

JOB_NAME   JOB_ACTION SCHEDULE_NAME        AUTO_ START_DATE      REPEAT_INTERVAL   END_DATE        LAST_START_DATE NEXT_RUN_DATE   ENABL STATE           LOGGING_LEV

---------- ---------- -------------------- ----- --------------- ----------------- --------------- --------------- --------------- ----- --------------- -----------

J131_4                W131_4               FALSE                                                   01-FEB-15 01.31                 TRUE  SCHEDULED       OFF

                                                                                                   .20.164953 AM +

                                                                                                   08:00

                                                                                                  

--job运行期间dba_scheduler_job_logdba_Scheduler_job_run_detailsdba_Scheduler_window_log同步输出的内容如下                                                                                                 

col job_name format a10

col operation format a7

col status format a10

col log_date format a50

col additional_info format a50

set linesize 170

select job_name,operation,status,log_date,additional_info from dba_scheduler_job_log where job_name in ('J131_4') order by log_date desc;

JOB_NAME   OPERATI STATUS     LOG_DATE                                           ADDITIONAL_INFO

---------- ------- ---------- -------------------------------------------------- --------------------------------------------------

J131_4     RUN     SUCCEEDED  01-FEB-15 01.31.20.181922 AM +08:00

J131_4     RUN     SUCCEEDED  01-FEB-15 01.28.20.182923 AM +08:00

J131_4     RUN     SUCCEEDED  01-FEB-15 01.26.16.294369 AM +08:00

 

col job_name format a10

col log_Date format a30

col req_start_date format a30

col actual_start_date format a30

col run_duration format a20

col additional_info format a30;

set linesize 170

select job_name,log_date,status,req_start_date,actual_start_date,run_duration,additional_info from dba_Scheduler_job_run_details where job_name in ('J131_4') order by log_date desc;

JOB_NAME   LOG_DATE                       STATUS                         REQ_START_DATE                 ACTUAL_START_DATE              RUN_DURATION

---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------

ADDITIONAL_INFO

------------------------------

J131_4     01-FEB-15 01.31.20.182513 AM + SUCCEEDED                      01-FEB-15 01.31.18.000000 AM + 01-FEB-15 01.31.20.165037 AM + +000 00:00:00

           08:00                                                         08:00                          08:00

 

 

J131_4     01-FEB-15 01.28.20.183339 AM + SUCCEEDED                      01-FEB-15 01.28.18.000000 AM + 01-FEB-15 01.28.20.166962 AM + +000 00:00:00

           08:00                                                         08:00                          08:00

 

 

J131_4     01-FEB-15 01.26.16.294804 AM + SUCCEEDED                      01-FEB-15 01.25.18.000000 AM + 01-FEB-15 01.26.16.270091 AM + +000 00:00:00

           08:00                                                         08:00                          08:00

 

col log_date format a50

col window_name format a15

col operation format a10

set linesize 150

select log_date,window_name,operation from dba_Scheduler_window_log where window_name='W131_4' order by log_date desc;

 

LOG_DATE                                           WINDOW_NAME     OPERATION

-------------------------------------------------- --------------- ----------

01-FEB-15 01.31.18.074558 AM +08:00                W131_4          OPEN

01-FEB-15 01.30.18.012337 AM +08:00                W131_4          CLOSE

01-FEB-15 01.28.18.074588 AM +08:00                W131_4          OPEN

01-FEB-15 01.27.18.012556 AM +08:00                W131_4          CLOSE

01-FEB-15 01.25.18.073275 AM +08:00                W131_4          OPEN

 

按照我们对于schedulewindow属性的设定值,窗口W131_4每隔1分钟打开一次,每次打开时长为2分钟,对于从dba_Scheduler_window_log反映出的信息可知窗口W131_4每次打开的时间点距离上一次打开的时间点相隔3分钟,窗口W131_4首次打开的时间是01-FEB-15 01.25.18.073275,而从dba_scheduler_job_log看出job J131_4首次执行的时间是01-FEB-15 01.26.16.294804,这个时间差是由于job J131_4创建完之后处于disable状态,直到01-FEB-15 01.26.16.294804这个时间点才被enable,所以造成了dba_Scheduler_job_run_detailslog_date=01-FEB-15 01.26.16.294804的那条记录REQ_START_DATEACTUAL_START_DATE字段有大约1分钟的时间差,在这个例子里,job实际的执行间隔时间是3分钟,其中2分钟来自于窗口duration=>interval '2' minute的定义,1分钟来自于Schedule repeat_interval=>'FREQ=MINUTELY'的定义。

 

 

--假设现在到了晚上,我们把jclass1这个job class关联到cgnight资源组

col tm format a40

set linesize 150

set pagesize 200

SQL> select * from tclass1 order by tm desc;

 

JNAME                SID_SERIAL                     RESOURCE_C TM

-------------------- ------------------------------ ---------- ----------------------------------------

J131_4               '1424-28025'                   CGDAY      01-FEB-15 04.30.34.198992 AM

J131_4               '1424-28011'                   CGDAY      01-FEB-15 04.27.34.192817 AM

J131_4               '1424-27997'                   CGDAY      01-FEB-15 04.24.34.016318 AM

J131_4               '1424-27983'                   CGDAY      01-FEB-15 04.21.33.550288 AM

.....省略了部分输出

 

exec dbms_scheduler.set_attribute('SYS.jclass1','resource_consumer_group','cgnight');

 

SQL> SELECT JOB_CLASS_NAME,RESOURCE_CONSUMER_GROUP from dba_scheduler_job_classes where job_class_name='JCLASS1';

 

JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP

------------------------------ ------------------------------

JCLASS1                        CGNIGHT

 

---稍等片刻发现接下来发起的job确实切换到了cgnight资源组

SQL> select * from tclass1 order by tm desc;

 

JNAME                SID_SERIAL                     RESOURCE_C TM

-------------------- ------------------------------ ---------- ----------------------------------------

J131_4               '1424-28077'                   CGNIGHT    01-FEB-15 04.42.34.195001 AM

J131_4               '1424-28063'                   CGNIGHT    01-FEB-15 04.39.34.172776 AM

J131_4               '1424-28049'                   CGDAY      01-FEB-15 04.36.34.199302 AM

J131_4               '1424-28035'                   CGDAY      01-FEB-15 04.33.34.195134 AM

J131_4               '1424-28025'                   CGDAY      01-FEB-15 04.30.34.198992 AM

J131_4               '1424-28011'                   CGDAY      01-FEB-15 04.27.34.192817 AM

J131_4               '1424-27997'                   CGDAY      01-FEB-15 04.24.34.016318 AM

J131_4               '1424-27983'                   CGDAY      01-FEB-15 04.21.33.550288 AM

 

--dba_scheduler_windows查看下一次窗口open的时间是01-FEB-15 04.51.32Enabled=TRUE,如果本次已经是最后一次open了,那么enabled显示为FALSE

SSQL> select window_name,enabled,active,LAST_START_DATE,NEXT_START_DATE from dba_scheduler_windows where window_name='W131_4';

 

WINDOW_NAM ENABL ACTIV LAST_START_DATE                     NEXT_START_DATE

---------- ----- ----- ----------------------------------- -----------------------------------

W131_4     TRUE  TRUE  01-FEB-15 04.51.32.002562 AM +08:00 01-FEB-15 04.51.32.000000 AM +08:00

 

/////////////////////

// 16、要使job正常执行,job的执行者必须对job里指定的programExecute权限,还要对program所指向的存储过程等对象有执行权限

/////////////////////

这里分为program_type=STORED_PROCEDUREprogram_type=EXECUTABLE两种场景

---------------1、先来看看program_type=STORED_PROCEDURE的情况----------

数据库里的两个用户scott2scott3

grant resource,connect,create job,unlimited tablespace to scott2 identified by "773946" ;

grant resource,connect,create job,unlimited tablespace to scott3 identified by "773946" ;

 

--connect as scott2,创建一个procedure名为proc2,把执行权限赋给scott3

create or replace procedure proc2

is

begin

dbms_output.put_line('scott2''s proc2');

end;

/

 

grant execute on proc2 to scott3;

 

--connect as scott2,创建一个program,把执行权限赋给scott3

exec dbms_scheduler.create_program(program_name=>'scott2_p201',program_type=>'STORED_PROCEDURE',program_action=>'scott2.proc2');

exec dbms_scheduler.enable('scott2.scott2_p201');

grant execute on scott2_p201 to scott3;

 

--connect as scott3,创建一个job,实质是通过scott2.scott2_p201调用Scott2.proc2,因此scott3既要有执行program scott2.scott2_p201的权限也要有执行procedure scott.proc2的权限,两者缺一不可

exec dbms_scheduler.drop_job(job_name=>'scott3_j201');

exec dbms_scheduler.create_job(job_name=>'scott3_j201',program_name=>'scott2.scott2_p201',start_date=>NULL,auto_drop=>FALSE,repeat_interval=>NULL,end_date=>NULL);

exec dbms_scheduler.enable('scott3_j201');

SQL> set serveroutput on

SQL> exec dbms_scheduler.run_job(job_name=>'scott3_j201');

scott2's proc2

 

PL/SQL procedure successfully completed.

 

--connect as scott2,如果取消Scott3scott2.proc2存储过程的执行权限

revoke execute on proc2 from scott3;

 

--connect as scott3,执行job时报ORA-00942

SQL> exec dbms_scheduler.run_job(job_name=>'scott3_j201');

BEGIN dbms_scheduler.run_job(job_name=>'scott3_j201'); END;

 

*

ERROR at line 1:

ORA-00942: table or view does not exist

ORA-06512: at "SYS.DBMS_ISCHED", line 185

ORA-06512: at "SYS.DBMS_SCHEDULER", line 486

ORA-06512: at line 1

 

--connect as scott2,如果取消Scott3scott2.scott2_p201 program的执行权限

revoke execute on scott2_p201 from scott3;

 

--connect as scott3,执行job时报ORA-27476

SQL> exec dbms_scheduler.run_job(job_name=>'scott3_j201');

BEGIN dbms_scheduler.run_job(job_name=>'scott3_j201'); END;

 

*

ERROR at line 1:

ORA-27476: "SCOTT2.SCOTT2_P201" does not exist

ORA-06512: at "SYS.DBMS_ISCHED", line 185

ORA-06512: at "SYS.DBMS_SCHEDULER", line 486

ORA-06512: at line 1

 

如果我们要让scott4用户执行scott3用户下的job scott3_j201的权限,赋予alter job的权限给scott4即可,无需把scott2.scott2_p201scott2.proc2执行权限赋给Scott4,因为当scott4执行Scott3.scott3_j201时就是以Scott3身份执行的

grant resource,connect,create job,unlimited tablespace to scott4 identified by "773946" ;

 

--connect as scott3

grant alter on scott3_j201 to scott4;

 

--connect as scott4

SQL> exec dbms_scheduler.run_job(job_name=>'scott3.scott3_j201');

scott2's proc2

 

PL/SQL procedure successfully completed.

 

 

---------------2、再看一下program_type=EXECUTABLE的情况----------

操作系统里tstdb1用户下有名为tstdb1.sh的脚本,脚本内容和执行权限如下

tstdb1@jq570322b:/home/tstdb1>cat tstdb1.sh

cat tstdb1.sh

echo scripts for tstdb1 > /tmp/$LOGNAME.`date +%m%d%H%M`

sleep 5

 

tstdb1@jq570322b:/home/tstdb1>ls -l tstdb1.sh

-rwxr-----    1 tstdb1   oinstall         24 Feb 01 21:52 tstdb1.sh

 

--赋予scott用户create external job权限

sqlplus '/as sysdba'

grant create external job to scott;

 

--Scott用户创建并运行脚本,提示没有权限

sqlplus scott/773946

exec dbms_scheduler.create_program(program_name=>'scott_201_p1',program_type=>'EXECUTABLE',program_action=>'/home/tstdb1/tstdb1.sh');

exec dbms_Scheduler.create_job(job_name=>'scott_201_j1',program_name=>'scott_201_p1',start_date=>NULL,repeat_interval=>NULL,end_date=>NULL,auto_drop=>FALSE);

exec dbms_Scheduler.enable('scott_201_p1,scott_201_j1');

exec dbms_Scheduler.run_job(job_name=>'scott_201_j1');

BEGIN dbms_Scheduler.run_job(job_name=>'scott_201_j1'); END;

 

*

ERROR at line 1:

ORA-27369: job of type EXECUTABLE failed with exit code: Permission denied

ORA-06512: at "SYS.DBMS_ISCHED", line 185

ORA-06512: at "SYS.DBMS_SCHEDULER", line 486

ORA-06512: at line 1

 

上面在执行dbms_Scheduler.run_job的同时,能在OS级别看到有nobody用户发起的调用/home/tstdb1/tstdb1.sh脚本的命令

tstdb1@jq570322b:/home/tstdb1>ps -ef|grep tstdb1.sh | grep -v grep

  nobody 36962418        1   0 22:26:44      -  0:00 extjob tstdb1 6357704 -exec /home/tstdb1/tstdb1.sh

上面的输出中6357704对应的是oracle shadow process的操作系统进程号,如果run_job时使用了use_current_session=FALSE,那么也能在dba_scheduler_job_run_details.slave_pid找到这个进程号,对于local external job,如果在创建job的时候不明确指定credential信息,且这个job创建在除了SYS用户之外的其它schema底下,会使用$ORACLE_HOME/rdbms/admin/externaljob.ora里定义的用户,这个用户默认情况下就是nobody

 

--给脚本赋上execute权限后,运行job又报了ORA-27369

chmod o+x+r /home/tstdb1/tstdb1.sh

 

tstdb1@jq570322b:/home/tstdb1>ls -l tstdb1.sh

-rwxr--r-x    1 tstdb1   oinstall         80 Feb 02 09:00 tstdb1.sh

 

SQL> exec dbms_Scheduler.run_job(job_name=>'scott_201_j1',use_current_session=>TRUE);

BEGIN dbms_Scheduler.run_job(job_name=>'scott_201_j1',use_current_session=>TRUE); END;

 

*

ERROR at line 1:

ORA-27369: job of type EXECUTABLE failed with exit code: 255

ORA-06512: at "SYS.DBMS_ISCHED", line 185

ORA-06512: at "SYS.DBMS_SCHEDULER", line 486

ORA-06512: at line 1

 

原因在于脚本里没有执行执行的shell,在tstdb1.sh脚本第一行加入#!/usr/bin/ksh后执行成功

SQL> exec dbms_Scheduler.run_job(job_name=>'scott_201_j1',use_current_session=>TRUE);

 

PL/SQL procedure successfully completed.

 

tstdb1@jq570322b:/tmp>ls -l /tmp/nobody*

-rw-r-----    1 nobody   nobody           19 Feb 02 09:10 /tmp/nobody.02020910

 

小提示:如果把job建在SYS用户下,那么默认会使用oracle software owner用户(比如oracle)发起脚本。

 

 

/////////////////////

// 17、同一个job class里的两个job,一个优先级高,一个优先级低,如果同时发起,是否低的会在高的之后发起

/////////////////////

sqlplus scott/773946

--创建测试表

create table t0202_1 (pri varchar2(10),tm timestamp);

 

--创建job classjob

exec DBMS_SCHEDULER.CREATE_JOB_CLASS(job_class_name=>'jc0202_1',logging_level=>DBMS_SCHEDULER.LOGGING_FULL);

 

exec dbms_scheduler.drop_job('j0202_1_high,j0202_1_low');

 

exec dbms_scheduler.create_job(job_name=>'j0202_1_high',job_class=>'jc0202_1',auto_drop=>FALSE,job_type=>'PLSQL_BLOCK',job_action=>'insert into t0202_1 values(''high_pri'',systimestamp);commit;',start_date=>to_timestamp_tz('20150202 15:20:00 +8:00','yyyymmdd hh24:mi:ss TZH:TZM'),repeat_interval=>'FREQ=SECONDLY;interval=5',end_date=>to_timestamp_tz('20150202 15:22:00 +8:00','yyyymmdd hh24:mi:ss TZH:TZM'));

 

exec dbms_scheduler.set_attribute('j0202_1_high','job_priority',1);

 

exec dbms_scheduler.create_job(job_name=>'j0202_1_low',job_class=>'jc0202_1',auto_drop=>FALSE,job_type=>'PLSQL_BLOCK',job_action=>'insert into t0202_1 values(''low_pri'',systimestamp);commit;',start_date=>to_timestamp_tz('20150202 15:20:00 +8:00','yyyymmdd hh24:mi:ss TZH:TZM'),repeat_interval=>'FREQ=SECONDLY;interval=5',end_date=>to_timestamp_tz('20150202 15:22:00 +8:00','yyyymmdd hh24:mi:ss TZH:TZM'));

exec dbms_scheduler.set_attribute('j0202_1_low','job_priority',5);

 

exec dbms_scheduler.enable('j0202_1_high,j0202_1_low');

 

--结果显示一个job class里的两个job发起时间并不一定是priority高的在前

SQL> select LOG_ID,req_start_date,ACTUAL_START_DATE,JOB_NAME from dba_scheduler_job_run_details where job_name like 'J0202%' order by log_id desc;

 

    LOG_ID REQ_START_DATE                           ACTUAL_START_DATE                        JOB_NAME

---------- ---------------------------------------- ---------------------------------------- --------------------

      3436 02-FEB-15 03.21.55.000000 PM +08:00      02-FEB-15 03.21.55.006149 PM +08:00      J0202_1_LOW

      3435 02-FEB-15 03.21.55.000000 PM +08:00      02-FEB-15 03.21.55.006115 PM +08:00      J0202_1_HIGH

      3434 02-FEB-15 03.21.50.000000 PM +08:00      02-FEB-15 03.21.50.005914 PM +08:00      J0202_1_LOW

      3433 02-FEB-15 03.21.50.000000 PM +08:00      02-FEB-15 03.21.50.005927 PM +08:00      J0202_1_HIGH

      3432 02-FEB-15 03.21.45.000000 PM +08:00      02-FEB-15 03.21.45.009104 PM +08:00      J0202_1_HIGH

      3431 02-FEB-15 03.21.45.000000 PM +08:00      02-FEB-15 03.21.45.009113 PM +08:00      J0202_1_LOW

      3430 02-FEB-15 03.21.40.000000 PM +08:00      02-FEB-15 03.21.40.009268 PM +08:00      J0202_1_LOW

      3429 02-FEB-15 03.21.40.000000 PM +08:00      02-FEB-15 03.21.40.009267 PM +08:00      J0202_1_HIGH

      3428 02-FEB-15 03.21.35.000000 PM +08:00      02-FEB-15 03.21.35.009188 PM +08:00      J0202_1_HIGH

      3427 02-FEB-15 03.21.35.000000 PM +08:00      02-FEB-15 03.21.35.009140 PM +08:00      J0202_1_LOW

      3426 02-FEB-15 03.21.30.000000 PM +08:00      02-FEB-15 03.21.30.009066 PM +08:00      J0202_1_HIGH

 

SQL> select * from t0202_1 where tm>to_date('20150202 15:21:35','yyyymmdd hh24:mi:ss') order by tm desc;

 

PRI        TM

---------- ---------------------------------------------------------------------------

low_pri    02-FEB-15 03.21.55.009320 PM

high_pri   02-FEB-15 03.21.55.009208 PM

low_pri    02-FEB-15 03.21.50.008531 PM

high_pri   02-FEB-15 03.21.50.008528 PM

high_pri   02-FEB-15 03.21.45.021677 PM

low_pri    02-FEB-15 03.21.45.011664 PM

low_pri    02-FEB-15 03.21.40.011878 PM

high_pri   02-FEB-15 03.21.40.011878 PM

high_pri   02-FEB-15 03.21.35.011766 PM

low_pri    02-FEB-15 03.21.35.011763 PM

 

/////////////////////////////////////////

// 18、测试database 异常宕机重启后能否继续未完成的job

/////////////////////////////////////////

--build test table and procedure

sqlplus scott/773946

 

drop table t0204_1;

create table t0204_1 (c1 timestamp);

 

create or replace procedure prc0204_1 as

begin

while ( true ) loop

insert into t0204_1 values(systimestamp);

commit;

dbms_lock.sleep(5);

end loop;

end;

/

 

---创建jobrestartable=TRUE一定要设置

exec dbms_scheduler.drop_job('J0204_1');

exec dbms_scheduler.drop_program('P0204_1');

exec dbms_scheduler.create_program(program_name=>'P0204_1',program_type=>'STORED_PROCEDURE',program_action=>'PRC0204_1');

exec dbms_scheduler.create_job(job_name=>'J0204_1',program_name=>'P0204_1',start_date=>systimestamp+0.5/1440,end_date=>systimestamp+10/1440,repeat_interval=>NULL,auto_drop=>FALSE);

exec dbms_scheduler.set_attribute('J0204_1','restartable',TRUE);

exec dbms_scheduler.enable('P0204_1,J0204_1');

select * from t0204_1;

 

SQL> select * from t0204_1;

 

C1

---------------------------------------------------------------------------

04-FEB-15 01.11.43.029742 PM

04-FEB-15 01.11.48.030364 PM

 

---restart database

shutdown abort

 

startup

 

---表里继续有数据insert进来,表明job自动恢复执行

SQL> select * from t0204_1;

 

C1

---------------------------------------------------------------------------

04-FEB-15 01.11.43.029742 PM

04-FEB-15 01.11.48.030364 PM

04-FEB-15 01.11.53.030663 PM

04-FEB-15 01.12.14.893963 PM    --->断点续跑

04-FEB-15 01.12.19.894506 PM

04-FEB-15 01.12.24.894893 PM

04-FEB-15 01.12.29.895404 PM

04-FEB-15 01.12.34.895867 PM

04-FEB-15 01.12.39.896308 PM

04-FEB-15 01.12.44.896671 PM

04-FEB-15 01.12.49.896998 PM

 

---J0204_1 job重启时的日志

col log_date format a40

col owner format a10

col job_name format a10

col operation format a10

col status format a10

col additional_info format a50

set linesize 170

set pagesize 120

select log_date,job_name,operation,status,additional_info from dba_scheduler_job_log where job_name='J0204_1' order by log_date desc;

LOG_DATE                                 JOB_NAME   OPERATION  STATUS     ADDITIONAL_INFO

---------------------------------------- ---------- ---------- ---------- --------------------------------------------------

04-FEB-15 01.12.14.550349 PM +08:00      J0204_1    RUN        STOPPED    REASON="ORA-01014: ORACLE shutdown in progress"

 

///////////////////////////////////////

///19exec dbms_Scheduler.enable用于jobcommit_semantics参数的测试

///////////////////////////////////////

 

###创建procedureprogramjob等对象,其中J0209_2故意指向一个不存在的program

exec dbms_scheduler.drop_job('j0209_1,j0209_2,j0209_3');

 

create or replace procedure PRC0209_1

as

aaa number;

begin

select count(*) into aaa from all_users;

end;

/

 

exec dbms_scheduler.create_program(program_name=>'P0209_1',program_type=>'STORED_PROCEDURE',program_action=>'PRC0209_1');

 

exec dbms_Scheduler.enable(name=>'P0209_1');

 

set serveroutput on

exec dbms_scheduler.create_job(job_name=>'j0209_1',program_name=>'P0209_1',auto_drop=>FALSE,repeat_interval=>'FREQ=MINUTELY;INTERVAL=15',end_date=>systimestamp+100/1440);

 

exec dbms_scheduler.create_job(job_name=>'j0209_2',program_name=>'P0209_111',auto_drop=>FALSE,repeat_interval=>'FREQ=MINUTELY;INTERVAL=15',end_date=>systimestamp+100/1440);

 

exec dbms_scheduler.create_job(job_name=>'j0209_3',program_name=>'P0209_1',auto_drop=>FALSE,repeat_interval=>'FREQ=MINUTELY;INTERVAL=15',end_date=>systimestamp+100/1440);

 

###使用STOP_ON_FIRST_ERROR Enable,结果仅有J0209_1 Enable

select job_name,enabled from dba_scheduler_jobs where job_name in ('J0209_1','J0209_2','J0209_3');

 

JOB_NAME                       ENABL

------------------------------ -----

J0209_3                        FALSE

J0209_2                        FALSE

J0209_1                        FALSE

 

exec dbms_Scheduler.enable(name=>'j0209_1,j0209_2,j0209_3',commit_semantics=>'STOP_ON_FIRST_ERROR');

select job_name,enabled from dba_scheduler_jobs where job_name in ('J0209_1','J0209_2','J0209_3');

JOB_NAME                       ENABL

------------------------------ -----

J0209_3                        FALSE

J0209_2                        FALSE

J0209_1                        TRUE

 

###disable'j0209_1,j0209_2,j0209_3'三个job,使用ABSORB_ERRORS Enable,结果J0209_1J0209_3 Enable

exec dbms_Scheduler.disable(name=>'j0209_1,j0209_2,j0209_3');

select job_name,enabled from dba_scheduler_jobs where job_name in ('J0209_1','J0209_2','J0209_3');

JOB_NAME                       ENABL

------------------------------ -----

J0209_3                        FALSE

J0209_2                        FALSE

J0209_1                        FALSE

 

exec dbms_Scheduler.enable(name=>'j0209_1,j0209_2,j0209_3',commit_semantics=>'ABSORB_ERRORS');

select job_name,enabled from dba_scheduler_jobs where job_name in ('J0209_1','J0209_2','J0209_3');

JOB_NAME                       ENABL

------------------------------ -----

J0209_3                        TRUE

J0209_2                        FALSE

J0209_1                        TRUE

 

###disable'j0209_1,j0209_2,j0209_3'三个job,使用TRANSACTIONAL Enable,结果没有Job Enable

exec dbms_Scheduler.disable(name=>'j0209_1,j0209_2,j0209_3');

JOB_NAME                       ENABL

------------------------------ -----

J0209_3                        FALSE

J0209_2                        FALSE

J0209_1                        FALSE

 

exec dbms_Scheduler.enable(name=>'j0209_1,j0209_2,j0209_3',commit_semantics=>'TRANSACTIONAL');

select job_name,enabled from dba_scheduler_jobs where job_name in ('J0209_1','J0209_2','J0209_3');

JOB_NAME                       ENABL

------------------------------ -----

J0209_3                        FALSE

J0209_2                        FALSE

J0209_1                        FALSE

 

////////////////////

// 20 dbms_scheduler.get_file包的功能测试

////////////////////

dbms_scheduler.get_file可用于一般文件的显示及copy,也可以用于查看及Copy具有credentialexternal job生成的日志

###使用dbms_scheduler.get_file 直接输出文件内容

grant create external job to ad;

 

set serveroutput on

declare

v_fileout clob;

begin

dbms_lob.createtemporary(lob_loc=>v_fileout,cache=>FALSE,dur=>DBMS_LOB.SESSION);

dbms_scheduler.get_file(source_file=>'/home/oracle/bes.lic.txt',source_host=>NULL,credential_name=>'C0208_3',file_contents=>v_fileout);

dbms_output.put_line(v_fileout);

end;

/

 

 

###使用dbms_scheduler.get_file copy文件至指定目录

grant read,write on directory hisdmp to ad;

 

exec dbms_scheduler.get_file(source_file=>'/home/oracle/bes.lic.txt',source_host=>NULL,credential_name=>'C0208_3',destination_file_name=>'bes.lic.txt.hisdmp',destination_directory_object=>'hisdmp');

 

oracle@jq570322b:/home/oracle>ls -lrt /oradata01/hisdmp/monthly/bes.lic.txt.hisdmp

-rw-r--r--    1 oracle   oinstall       1438 Feb 09 15:15 /oradata01/hisdmp/monthly/bes.lic.txt.hisdmp

 

 

////////////////////

// 21、使用dbms_scheduler.drop_job drop job_class时,commit_semantics参数只能指定'STOP_ON_FIRST_ERROR'

////////////////////

 

####创建job

create or replace procedure prc0209_11(v_str in varchar2)

as

begin

dbms_output.put_line(v_str);

end;

/

 

exec dbms_scheduler.create_job_class(job_class_name=>'JC0209_1');

exec dbms_scheduler.create_job(job_name=>'J0209_11',job_class=>'JC0209_1',program_name=>'P0209_11',auto_drop=>FALSE,repeat_interval=>'FREQ=MINUTELY;INTERVAL=15',end_date=>systimestamp+100/1440);

exec dbms_scheduler.create_job(job_name=>'J0209_12',job_class=>'JC0209_1',program_name=>'P0209_11',auto_drop=>FALSE,repeat_interval=>'FREQ=MINUTELY;INTERVAL=15',end_date=>systimestamp+100/1440);

exec dbms_scheduler.create_program(program_name=>'P0209_11',program_type=>'STORED_PROCEDURE',program_action=>'PRC0209_11',number_of_arguments=>1);

exec dbms_scheduler.define_program_argument(program_name=>'P0209_11',argument_position=>1,argument_type=>'VARCHAR2');

exec dbms_scheduler.set_job_argument_value(job_name=>'J0209_11',argument_position=>1,argument_value=>'J0209_11');

exec dbms_scheduler.set_job_argument_value(job_name=>'J0209_12',argument_position=>1,argument_value=>'J0209_12');

exec dbms_scheduler.enable('P0209_11');

set serveroutput on

exec dbms_scheduler.run_job(job_name=>'J0209_11');

exec dbms_scheduler.run_job(job_name=>'J0209_12');

 

####drop job class

exec dbms_scheduler.drop_job(job_name=>'SYS.JC0209_1',commit_semantics=>'TRANSACTIONAL');

*

ERROR at line 1:

ORA-27361: scheduler API invoked with illegal or inconsistent arguments

ORA-06512: at "SYS.DBMS_ISCHED", line 6652

ORA-06512: at "SYS.DBMS_SCHEDULER", line 651

ORA-06512: at line 1

 

 

exec dbms_scheduler.drop_job(job_name=>'SYS.JC0209_1',commit_semantics=>'ABSORB_ERRORS');

*

ERROR at line 1:

ORA-27361: scheduler API invoked with illegal or inconsistent arguments

ORA-06512: at "SYS.DBMS_ISCHED", line 6652

ORA-06512: at "SYS.DBMS_SCHEDULER", line 651

ORA-06512: at line 1

 

---只能使用STOP_ON_FIRST_ERROR才成功

exec dbms_scheduler.drop_job(job_name=>'SYS.JC0209_1',commit_semantics=>'STOP_ON_FIRST_ERROR');

 

///////////////////////////////////////

/// 22、合理使用dbms_Scheduler.drop_job里的defer参数避免对于正在运行的job进行drop_job操作时出错

///////////////////////////////////////

create or replace procedure PRC0209_4

as

begin

while ( true ) loop

dbms_output.put_line('AAA');

dbms_lock.sleep(3);

end loop;

end;

/

 

exec dbms_scheduler.create_program(program_name=>'P0209_4',program_type=>'STORED_PROCEDURE',program_action=>'PRC0209_4');

exec dbms_scheduler.create_job(job_name=>'J0209_4',program_name=>'P0209_4',auto_drop=>FALSE);

exec dbms_scheduler.enable('P0209_4');

exec dbms_scheduler.run_job(job_name=>'J0209_4',use_current_session=>FALSE);

 

---drop job 不指定forcedefer参数,默认就是force=>FALSEdefer=>FALSE,这种情况下不能drop一个正在运行的job

exec dbms_scheduler.drop_job('J0209_4');

BEGIN dbms_scheduler.drop_job('J0209_4'); END;

 

*

ERROR at line 1:

ORA-27478: job "AD.J0209_4" is running

ORA-06512: at "SYS.DBMS_ISCHED", line 213

ORA-06512: at "SYS.DBMS_SCHEDULER", line 657

ORA-06512: at line 1

 

---drop job 不指定defer=>TRUE参数,这种情况下drop不会报错,会等到job运行结束

exec dbms_scheduler.drop_job(job_name=>'J0209_4',defer=>TRUE);

 

PL/SQL procedure successfully completed.

 

SQL> select job_name from dba_scheduler_running_jobs;

 

JOB_NAME

------------------------------

J0209_4

 

---stop_job后,观察这个job已经不存在了

exec dbms_scheduler.stop_job(job_name=>'J0209_4');

 

PL/SQL procedure successfully completed.

 

 

SQL> select job_name from dba_scheduler_jobs where job_name='J0209_4'

 

no rows selected

 

///////////////////

// 23、使用dbms_scheduler.disable禁用job时,如果force=>TRUE,那么忽略commit_semantics的参数值

///////////////////

create or replace procedure PRC0210_1

as

begin

while ( true ) loop

dbms_output.put_line('AAA');

dbms_lock.sleep(3);

end loop;

end;

/

 

create or replace procedure PRC0210_2

as

begin

dbms_output.put_line('BBB');

dbms_lock.sleep(3);

end;

/

 

exec dbms_scheduler.create_program(program_name=>'P0210_1',program_type=>'STORED_PROCEDURE',program_action=>'PRC0210_1');

exec dbms_scheduler.create_program(program_name=>'P0210_2',program_type=>'STORED_PROCEDURE',program_action=>'PRC0210_2');

 

exec dbms_scheduler.create_job(job_name=>'J0210_1',program_name=>'P0210_1',repeat_interval=>'FREQ=MINUTELY',end_date=>sysdate+100/1440);

exec dbms_scheduler.create_job(job_name=>'J0210_2',program_name=>'P0210_2',repeat_interval=>'FREQ=MINUTELY;interval=10',end_date=>sysdate+100/1440);

 

exec dbms_scheduler.enable('P0210_1,P0210_2,J0210_1,J0210_2');

select job_name,enabled from dba_scheduler_jobs where job_name in ('J0210_1','J0210_2');

 

 

 

--J0210_1调度起来后,disable时提示job is running

exec dbms_scheduler.disable('J0210_1,J0210_2');

BEGIN dbms_scheduler.disable('J0210_1,J0210_2'); END;

 

*

ERROR at line 1:

ORA-24101: stopped processing the argument list at: J0210_1

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.DBMS_SCHEDULER", line 2752

ORA-27478: job "AD.J0210_1" is running

ORA-06512: at line 1

 

select job_name from dba_Scheduler_running_jobs;

 

JOB_NAME

------------------------------

J0210_1

 

set linesize 180

select job_name,enabled from dba_scheduler_jobs where job_name in ('J0210_1','J0210_2');

JOB_NAME                       ENABL

------------------------------ -----

J0210_1                        TRUE

J0210_2                        TRUE

 

--使用force=>TRUE,同时制定commit_semantics=>'STOP_ON_FIRST_ERROR'disable成功

exec dbms_scheduler.disable(name=>'J0210_1,J0210_2',force=>TRUE,commit_semantics=>'STOP_ON_FIRST_ERROR');

 

PL/SQL procedure successfully completed.

 

---状态变成enabled=FALSE了,但之前的job还在运行

select job_name,enabled from dba_scheduler_jobs where job_name in ('J0210_1','J0210_2')

 

JOB_NAME                       ENABL

------------------------------ -----

J0210_1                        FALSE

J0210_2                        FALSE

 

---disable前发起的job仍在运行

SQL> select job_name from dba_Scheduler_running_jobs;

 

JOB_NAME

------------------------------

J0210_1

 

 

///////////////////

// 24external job里使用了credential_namejob执行时才会在$ORACLE_HOME/scheduler/log目录下生成_stdout_stderr文件

///////////////////

###未使用credential_nameexternal job不会在在$ORACLE_HOME/scheduler/log目录下生成job_X_stderr或者job_X_stdout文件

oracle@jq570322b:/home/oracle>cat test.sh

#!/usr/bin/ksh    

date > /home/oracle/test.log

date

 

chmod o+x test.sh

 

---创建job时不指定credential参数

exec dbms_scheduler.create_job(job_name=>'J0210_3',job_type=>'EXECUTABLE',job_action=>'/home/oracle/test.sh',repeat_interval=>'FREQ=SECONDLY;INTERVAL=30',end_date=>sysdate+100/1440);

exec dbms_scheduler.enable('J0210_3');

 

---目录$ORACLE_HOME/scheduler/log下为空

 

###使用了credential_nameexternal job才能在$ORACLE_HOME/scheduler/log目录下生成job_6427235_65634_stderr或者job_6427235_65634_stdout文件

exec dbms_scheduler.create_credential(credential_name=>'C0210_3',username=>'oracle',password=>'uiop7890');

exec dbms_scheduler.stop_job('J0210_3');

exec dbms_scheduler.disable('J0210_3');

exec dbms_scheduler.set_attribute('J0210_3','credential_name','C0210_3');

exec dbms_scheduler.enable('J0210_3');

 

col log_date format a50

col job_name format a10

col additional_info format a60

col credential_name format a10

col status format a10

set linesize 180

set pagesize 200

set pause on

select job_name,credential_name,status,log_date,additional_info from dba_scheduler_job_run_details where job_name='J0210_3' order by log_date desc;

JOB_NAME   CREDENTIAL STATUS     LOG_DATE                                           ADDITIONAL_INFO

---------- ---------- ---------- -------------------------------------------------- ------------------------------------------------------------

J0210_3    C0210_3    SUCCEEDED  10-FEB-15 11.53.12.071569 AM +08:00                EXTERNAL_LOG_ID="job_6427235_65646",

                                                                                    USERNAME="oracle",

                                                                                    STANDARD_ERROR="Permission denied

                                                                                    /home/oracle/test.sh[2]: /home/oracle/test.log: cannot creat

                                                                                    e

                                                                                  "

oracle@jq570322b:/oracle/app/oracle/product/11.2.0/db_1/scheduler/log>cat job_6427235_65646_stderr

Permission denied

/home/oracle/test.sh[2]: /home/oracle/test.log: cannot create    

 

--修正上述权限错误后,J0210_3重新运行,后生成_stdout文件

oracle@jq570322b:/oracle/app/oracle/product/11.2.0/db_1/scheduler/log>ls -rlt job*_stdout

-rw-rw----    1 oracle   oinstall         29 Feb 10 12:03 job_6427235_65677_stdout

-rw-rw----    1 oracle   oinstall         29 Feb 10 12:04 job_6427235_65679_stdout

-rw-rw----    1 oracle   oinstall         29 Feb 10 12:04 job_6427235_65680_stdout

-rw-rw----    1 oracle   oinstall         29 Feb 10 12:05 job_6427235_65682_stdout

oracle@jq570322b:/oracle/app/oracle/product/11.2.0/db_1/scheduler/log>cat job_6427235_65682_stdout

Tue Feb 10 04:05:12 UTC 2015

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1438478/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1642094