设为首页收藏本站

Botang唐波's Oracle Station

查看: 273|回复: 0

Oracle Scheduler

[复制链接]

745

主题

1118

帖子

8120

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8120
发表于 2018-8-9 21:22:22 | 显示全部楼层 |阅读模式
1.   普通的基于时间的作业:
  1. BEGIN
  2. DBMS_SCHEDULER.CREATE_PROGRAM(
  3. program_name=>'"HR"."PROGRAM1"',
  4. program_action=>'begin
  5. update employees set salary=salary+1 where employee_id=100;
  6. commit;
  7. end;',
  8. program_type=>'PLSQL_BLOCK',
  9. number_of_arguments=>0,
  10. comments=>'program1',
  11. enabled=>TRUE);
  12. END;
复制代码
  1. BEGIN
  2. sys.dbms_scheduler.create_schedule(
  3. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
  4. start_date => to_timestamp_tz('2018-08-09 21:25:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
  5. end_date => to_timestamp_tz('2018-08-09 21:28:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
  6. comments => 'schedule1',
  7. schedule_name => '"HR"."SCHEDULE1"');
  8. END;
复制代码
  1. BEGIN
  2. sys.dbms_scheduler.create_job(
  3. job_name => '"HR"."JOB1"',
  4. program_name => '"HR"."PROGRAM1"',
  5. schedule_name => '"HR"."SCHEDULE1"',
  6. job_class => '"DEFAULT_JOB_CLASS"',
  7. comments => 'job1',
  8. auto_drop => FALSE,
  9. enabled => TRUE);
  10. END;
复制代码
Operation Detail























[table]
























SelectLog IDLog DateOperationStatus








389Aug 9, 2018 9:27:30 PM +08:00COMPLETED























388Aug 9, 2018 9:27:30 PM +08:00RUNSUCCEEDED








387Aug 9, 2018 9:27:00 PM +08:00RUNSUCCEEDED








386Aug 9, 2018 9:26:30 PM +08:00RUNSUCCEEDED








385Aug 9, 2018 9:26:00 PM +08:00RUNSUCCEEDED








384Aug 9, 2018 9:25:30 PM +08:00RUNSUCCEEDED








383Aug 9, 2018 9:25:00 PM +08:00RUNSUCCEEDED

2.   外部的基于时间的作业:
/home/oracle/job2.sh
  1. #!/bin/sh

  2. date +%Y%m%d-%H%M%S >> /home/oracle/job2.log
复制代码
  1. grant create external job to hr;
复制代码
  1. select  * from dba_scheduler_credentials;
复制代码
默认为空。
  1. begin
  2.    dbms_scheduler.create_credential(credential_name => 'credential1',
  3.    username => 'oracle',
  4.    password => 'oracle');
  5. end;
复制代码
  1. select  * from dba_scheduler_credentials;
复制代码
   OWNERCREDENTIAL_NAMEUSERNAMEDATABASE_ROLEWINDOWS_DOMAINCOMMENTS
1SYSCREDENTIAL1oracle

  1. grant execute on credential1 to hr;
复制代码
  1. BEGIN
  2. DBMS_SCHEDULER.CREATE_PROGRAM(
  3. program_name=>'"HR"."PROGRAM2"',
  4. program_action=>'/home/oracle/job2.sh',
  5. program_type=>'EXECUTABLE',
  6. number_of_arguments=>0,
  7. comments=>'PROGRAM2',
  8. enabled=>TRUE);
  9. END;
复制代码
  1. BEGIN
  2. sys.dbms_scheduler.create_schedule(
  3. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
  4. start_date => to_timestamp_tz('2018-08-09 21:44:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
  5. end_date => to_timestamp_tz('2018-08-09 21:46:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
  6. comments => 'schedule2',
  7. schedule_name => '"HR"."SCHEDULE2"');
  8. END;
复制代码
  1. BEGIN
  2. sys.dbms_scheduler.create_job(
  3. job_name => '"HR"."JOB2"',
  4. program_name => '"HR"."PROGRAM2"',
  5. schedule_name => '"HR"."SCHEDULE2"',
  6. job_class => '"DEFAULT_JOB_CLASS"',
  7. comments => 'job2',
  8. auto_drop => FALSE,
  9. enabled => FALSE);
  10. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB2"', attribute => 'credential_name', value => '"SYS"."CREDENTIAL1"' );
  11. sys.dbms_scheduler.enable( '"HR"."JOB2"' );
  12. END;
复制代码
Operation Detail
[table=98%]
[table]

SelectLog IDLog DateOperationStatus
394Aug 9, 2018 9:45:30 PM +08:00COMPLETED
393Aug 9, 2018 9:45:30 PM +08:00RUNSUCCEEDED
392Aug 9, 2018 9:45:00 PM +08:00RUNSUCCEEDED
391Aug 9, 2018 9:44:30 PM +08:00RUNSUCCEEDED
390Aug 9, 2018 9:44:00 PM +08:00RUNSUCCEEDED
[/td][/tr]
[/table]






回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-12-13 16:40 , Processed in 0.110456 second(s), 24 queries .

快速回复 返回顶部 返回列表