Oracle Scheduler学习笔记分享
初次接触oracle scheduler会觉得这东西并不难，但是涉及到方方面面的概念比较多，看似比较分散的知识点间往往又存在较为紧密的关联，技术细节的掌握成为了用好oracle scheduler的关键。下面是我在oracle scheduler学习过程中的总结和提炼出的一些内容，希望对正在学习oracle scheduler的同学有所帮助。如果对基本概念不是很清楚建议还是先看一下oracle的官方文档。
第一部分：Oracle scheduler体系结构里的各个对象类型及其使用方法
1. Program
避免在每个job定义时都在job_action参数定义一长pl/sql代码或者shell命令，也为了在不同的job间实现对相同程序的复用；
其中的STORED_PROCEDURE、EXTERNAL类型能够接收由define_program_argument、define_metadata_argument这两个过程所定义的外部参数类型，PLSQL_BLOCK类型不适用上述两个过程，但能在其代码里直接使用job_name、job_subname、job_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_Date、end_date、repeat_interval等参数，对于执行时间相同的能够共用同一个schedule；
Scheduler创建完以后就是enabled，且能够被任何用户使用，不需要额外赋权；
创建scheduler的repeat_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_spec、event_condition两个参数指定队列名称及队列中的事件条件，但后续如果要使用set_attribute修改这两个参数时，统一通过event_spec这一个参数进行修改，这个参数有value、value2两个值，value对应queue_spec、value2对应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能得到更合理的资源分配。
创建Window时duration属性表示每一次窗口打开的持续时间，最小单位为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_time、window所引用的schedule被drop掉的情况下window才会被disabled；
如果要让window打开时resource_plan不发生变化，必须指定alter system set resoure_manager_plan=force:plan名称，强制系统保留在当前的resource_plan；
当window close时默认情况下不会停止在这个窗口发起的job，除非在创建job时指定了stop_on_window_close=TRUE，但要注意如果window在job完成前就关闭了，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属性默认为TRUE，auto_drop=TRUE表示在job运行结束后自动drop掉，但有一种情况例外：当使用named schedule方式定义job的运行时间，且这个schedule是一个window，这种情况下即使auto_drop设为TRUE，job运行完成后也不会被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.state为completed和succeeded间的区别是，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_CLASS的job class而这个job class的logging_level是RUNS—表示记录运行期间的日志；
使用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类型为chain、job里包含detached program、或者job是在远程主机或数据库上执行这三种情况下，必须在run_job时指定use_current_session=TRUE，比如dbms_scheduler.run_job(job_name=>'job1',use_current_session=>TRUE)，这时虽然是手工运行job，dba_scheduler_jobs视图里的run_count、last_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 class的logging_level和单个job设置的logging_level取值不同时，记录信息多的那个设置生效
6. Chain
多个program的集合，每个program称为一个step，通过rule来定义这些step的执行顺序，要使得chain能够开始执行，必须有一个rule的condition设置为TRUE。要是chain能够正常结束，必须有一个rule的action为'END'，否则dba_scheduler_jobs里的state将显示为CHAIN_STALLED，表示chain处于既没有step在运行也没有正常结束的状态；
Create_chain里的Evaluation_interval参数，是一个比较有用的选项，能够对chain 定义之外的对象进行定时监测，如果满足条件则可以触发某个step运行，例如下面的定义表示每1分钟监测一次trigtab表的记录数，如果大于0且step 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 running和start的特点
7. Credential
将OS、DB层面的用户名和口令定义到访问身份证明里之后对于运行local/remote external jobs和remote 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 watcher的job收到这条消息后便会自动开始运行。去目录下探测是否有新文件生成的工作由SYS用户下名为FILE_WATCHER_SCHEDULE的job来完成，缺省时每隔10分钟探测一次，这一时间间隔可以使用如下方法进行调整： dbms_scheduler.set_attribute('SYS.FILE_WATCHER_SCHEDULE','repeat_interval','FREQ=MINUTELY');最小可以调整为每分钟1次，访问sys.aq$scheduler_filewatcher_qt表可以查看已经入到队列的消息内容；
基于file watcher的job在其Parallel_instances属性为FALSE(缺省值)的情况下永远只处理时间戳最新的一个文件，假设FILE_WATCHER_SCHEDULE的时间间隔为1分钟，那么一分钟内目录下陆续到达了3个文件只有时间最新的那个文件会被处理，其它两个会被忽略，如果要一个不落的逐个处理必须将parallel_instances设为TRUE，这样会启动多个instances并行处理每个文件；
10. Group
可以为window、destination创建group，使用window group、destination group可以在创建job时为job同时定义包含多个Destination的destination group作为其destination_name，也可以定义包含多个window的window group作为其schedule_name，省去了针对单个destination或window进行逐一定义的繁琐
第二部分：Oracle scheduler里的权限
除非创建时将名称放在双引号里表示严格区分大小，否则创建出来的oracle schedule对象名称都是以大写形式存放在数据字典里的。
job class、window、类型为window 的group三类对象其schema都是sys，当引用window和类型为window的group时必须在名称前加上SYS。其它对象都是创建在哪个schema下就属于哪个schema。
Job、 chain、program三类对象创建完之后默认均为disabled状态，其余对象建完后都是立即可用的状态
Schedule、destination、window、group(window类型)四类对象的使用权限是grant给public的，所有用户都能使用，比如A用户建完一个名为DestA的Destination，B用户不用被授予DestA的任何对象权限也能使用DestA
对于系统权限的使用归纳如下：
|
System privilege |
System privilege能做什么 |
|
Create job |
在自己的schema下创建job、chain、schedule、program、file_watcher、credential、destination、group |
|
无需任何权限 |
修改和删除 自己schema下的job、chain、schedule、program、file_watcher、credential、destination、group |
|
Create any job |
在别的schema下（SYS除外）创建、修改、运行、删除job、chain、schedule、program、file_watcher、credential、destination、group， |
|
Create external job |
创建job_action=Executable的job或者指向的program_type=executable类型的job |
|
Create job Create external job |
运行external job |
|
Execute any program |
有权使用任何用户下的program |
|
Execute any class |
有权使用SYS用户下的所有job class |
|
Manage scheduler |
创建/修改/删除 job class、window、group(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; |
第三部分：一些有助于概念理解的小测试
///////////////////////////////////////////////////////////////////////////////////////////////
/// 1、11.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
然后设置一个即将open的window，在window open前的几秒钟就能在alert.log里看到Starting background process CJQ0的信息
//////////////////////
// 2、dba_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.
///////////////////////////////////
// 3、dba_scheduler_job_log、dba_scheduler_job_run_details内容上的区别
// dba_Scheduler_window_log、dba_Scheduler_window_details内容上的区别
///////////////////////////////////
###dba_scheduler_job_log、dba_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_log和dba_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_817，AT_SQ_SQL_SW_817归属于名为ORA$AT_JCNRM_SQ的job classes，ORA$AT_JCNRM_SQ的log_level为FULL，所以我们看到在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_WINDOW，disable和enable的操作都是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_log、dba_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;
///////////////////////////////////
// 5、stop_on_window_close=TRUE/FALSE决定了window close的时候job是否会终止
///////////////////////////////////
###stop_on_window_close=TRUE，close window后job也随之停止
--创建测试表
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
--close掉w0202_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=FALSE，close window后job继续运行
--在上述测试的基础上，设置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
///////////////////////////////////
// 6、define_program_argument用于inline方式定义job_action和非inline方式定义job_action的job，这两种情况下有何区别
///////////////////////////////////
create or replace procedure prc0204_2 (v_info in varchar2)
as
begin
dbms_output.put_line(v_info);
end;
/
###inlined program方式定义job_action，job_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_action，program_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_argument里metadata_attribute中除了event_message以外字段，包括job_name、job_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_name、job_start 两个metadata字段，输出了job的名称和执行开始时间
///////////////////////////////////
// 7、如果遇到program里的参数不是varchar2类型的，那么必须使用SET_JOB_ANYDATA_VALUE来赋值
///////////////////////////////////
exec dbms_Scheduler.drop_job('LJ0213_3');
exec dbms_Scheduler.drop_program('P0213_3');
--创建procedure、program、job等对象
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_run把detached job的执行结果通知主job
---创建测试表
create table t0204_4 (c1 timestamp);
---创建procedure，procedure里没有包含通知主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;
/
--创建program、job
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_RUN后dba_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 job的job_action必须定义为一个program_name，program_type必须为PLSQL_BLOCK或者STORED_PROCEDURE，lightweight 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，所以scott2、scott3都有权限执行J0204_6，当然scott2、Scott3用户必须对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_1的event schedule
cha0205_1:这是一个chain，里面包含了st0205_1、st0205_2、st0205_3、st0205_4四个chain step
est0205_1:这是一个基于event的schedule，由J0205_0成功完成后触发
st0205_2，这是cha0205_1里基于event的step，由名为est0205_1的event schedule触发
st0205_3，这是cha0205_1里的program step
st0205_4，这是cha0205_1里的program step
st0205_1是cha0205_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_1，st0205_1由Schedule: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_2、st0205_3、st0205_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=chain的J0205_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=chain的J0205_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
///////////////////////////
两个job：J0205_3、J0205_4，其中J0205_3启动后及完成后均会自动触发J0205_4运行，J0205_3启动和完成阶段产生的事件消息会进入名为SYS.SCHEDULER$_EVENT_QUEUE的队列，该队列专门用于存放scheduler运行期间产生的event message，J0205_4的owner必须有权限从这个队列中把和自己有关的event message Dequeue出来如果判断是J0205_3发来的则触发J0205_4开始运行
---创建测试表
create table t0205_3 (c1 varchar2(10),tm timestamp,event_info varchar2(500));
---创建J0205_3运行所需的procedure、program
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队列的权限，创建agent，agent名称为ad_agent(两种方法二选一)
***SYS用户执行
exec DBMS_AQADM.ENABLE_DB_ACCESS('ad_agent','ad');
***也可以使用scott用户执行
exec DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER('ad_agent');
--创建基于event的schedule，这个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运行所需的procedure、program，将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所有program和job
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_3的job运行，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;
/
--创建credential、file_watcher、program
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.txt的file_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 program、chain、step
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_5于09.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 job的remote主机上安装好schedule agent并完成注册，具体可参照。。。。。？？？？,jq570321a为remote host，jq570322b为job所在的数据库服务器
---在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地址和主机名对应关系，这一步很重要决定了job在remote 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用户创建program、credential、job等对象，destination_name为Schedule 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进行优先级调整的目的
/////////////////////
假设名为jclass1的job 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'; --关闭所有open的window
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_class和window永远是创建在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，默认情况下job、program创建出来都是disabled状态，job的log-level=OFF，但job_class的log-level是RUNS，这里以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 job、program
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_jobs的enable属性从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_log、dba_Scheduler_job_run_details、dba_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
按照我们对于schedule及window属性的设定值，窗口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_details里log_date=01-FEB-15 01.26.16.294804的那条记录REQ_START_DATE和ACTUAL_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.32，Enabled=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里指定的program有Execute权限，还要对program所指向的存储过程等对象有执行权限
/////////////////////
这里分为program_type=STORED_PROCEDURE和program_type=EXECUTABLE两种场景
---------------1、先来看看program_type=STORED_PROCEDURE的情况----------
数据库里的两个用户scott2和scott3
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，如果取消Scott3对scott2.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，如果取消Scott3对scott2.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_p201、scott2.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 class、job
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;
/
---创建job，restartable=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"
///////////////////////////////////////
///19、exec dbms_Scheduler.enable用于job时commit_semantics参数的测试
///////////////////////////////////////
###创建procedure、program、job等对象，其中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_1、J0209_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具有credential的external 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 不指定force和defer参数，默认就是force=>FALSE、defer=>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
///////////////////
// 24、external job里使用了credential_name，job执行时才会在$ORACLE_HOME/scheduler/log目录下生成_stdout、_stderr文件
///////////////////
###未使用credential_name的external 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_name的external 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博客 ” ，链接：https://blog.itpub.net/53956/viewspace-1438478/ ，如需转载，请注明出处，否则将追究法律责任。
