设为首页收藏本站

Botang唐波's Oracle Station

查看: 68|回复: 0

活动第45/46次:scheduler(2018-08-04星期六上下午)

[复制链接]

708

主题

1070

帖子

7759

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7759
发表于 2018-7-28 16:32:01 | 显示全部楼层 |阅读模式
1. 基于时间的schedule:
  1.         
  2.                         
  3. BEGIN
  4. sys.dbms_scheduler.create_schedule(
  5. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
  6. start_date => systimestamp at time zone 'Asia/Shanghai',
  7. end_date => to_timestamp_tz('2018-07-28 16:35:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
  8. comments => 'schedule1',
  9. schedule_name => '"HR"."SCHEDULE1"');
  10. END;
复制代码
2. 基于时间的外部作业
(如果在windows上做:1. 打开组策略,用gpedit.msc命令。
2. 逐步找到compute configuration -- windows setting -- security setting -- local policy
-- user rights assignment -- log on as a batch job
3.双击"log on as a batch job",在这里加入你需要的操作系统user
4.再次在OEM的Host Credentials输入那些user和password,一切ok。)

  1. grant create job to hr;

  2. grant create external job to hr;
复制代码
  1. begin
  2.    dbms_scheduler.create_credential(credential_name =>  'credential1',
  3.    username => 'oracle',
  4.    password => 'oracle');
  5. end;
  6.    
复制代码
   OWNERCREDENTIAL_NAMEUSERNAMEDATABASE_ROLEWINDOWS_DOMAINCOMMENTS
1SYSCREDENTIAL1oracle

  1. grant execute on credential1 to hr;
复制代码
写一个shell脚本:
  1. #!/bin/sh

  2. export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
  3. export ORACLE_SID=orcl
  4. export ORACLE_BASE=/u01/app/oracle

  5. /u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus /nolog >> /home/oracle/job2a.log <<EOF
  6. conn hr/oracle_4U
  7. alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
  8. select  sysdate from dual;
  9. exit
  10. EOF

  11. date +%Y%m%d-%H%M%S >> /home/oracle/job2b.log
复制代码
  1.         
  2.                         
  3. BEGIN
  4. DBMS_SCHEDULER.CREATE_PROGRAM(
  5. program_name=>'"HR"."PROGRAM2"',
  6. program_action=>'/home/oracle/job2.sh',
  7. program_type=>'EXECUTABLE',
  8. number_of_arguments=>0,
  9. comments=>'program2',
  10. enabled=>TRUE);
  11. END;
复制代码

跟schedule1类似:
  1. BEGIN
  2. sys.dbms_scheduler.create_schedule(
  3. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
  4. start_date => systimestamp at time zone 'Asia/Shanghai',
  5. end_date => to_timestamp_tz('2018-07-28 16:52:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
  6. comments => 'schedule2',
  7. schedule_name => '"HR"."SCHEDULE2"');
  8. END;
复制代码
a.png


3. 基于日历的schedule:
  1. BEGIN
  2. /* Complex scheduling example 1: Public Holidays */
  3. DBMS_SCHEDULER.CREATE_SCHEDULE
  4. (schedule_name => 'HR.SCHEDULE3',
  5. repeat_interval =>'freq=YEARLY;bydate=0701,0801,1001'
  6. ,comments => 'Politics Days'
  7. );
  8. END;
复制代码
  1. BEGIN
  2. /* Complex scheduling example 2: Working Days */
  3. DBMS_SCHEDULER.CREATE_SCHEDULE
  4. (schedule_name => 'HR.SCHEDULE31',
  5. repeat_interval =>'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;'
  6. ,comments => 'Run on every working day'
  7. );
  8. END;
复制代码
  1. 'FREQ=YEARLY;BYYEARDAY=1,32,-306'
  2. The following example creates a schedule consisting of the first working day of every month in 2008:
  3. 'freq=MONTHLY;byday=MON,TUE,WED,THU,FRI;bysetpos=1'
  4. 1st and 3rd Monday each month:
  5. 'freq=MONTHLY;byday=1MON,3MON'
  6. The following example creates a schedule consisting of the last day of each quarter:
  7. 'freq=YEARLY;bymonth=MAR,JUN,SEP,DEC;bymonthday=-1'
复制代码
  1. declare
  2.    v_1  timestamp;
  3. begin
  4.   for i in 1..3
  5.    loop
  6.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (  
  7.     calendar_string=>'FREQ=yearly;bydate=0701,0801,1001;BYHOUR=14;
  8.                     BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31'   ,
  9.    start_date => sysdate,
  10.    return_date_after  => v_1,
  11.    next_run_date    => v_1 );
  12.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
  13. end loop;
  14. end;


  15. begin
  16.    dbms_scheduler.create_schedule(schedule_name => 'HR.SCHEDULE32',
  17.    repeat_interval => 'FREQ=yearly;bydate=0701,0801,1001;BYHOUR=14;
  18.                     BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31' );
  19. end;
  20.    
复制代码
-----------------------
4. 为了做基于事件的schedule,需要先制造queue:
  1. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;

  2. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';

  3. select  * from dba_queues q where q.QUEUE_TYPE   not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');

  4. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
  5.   event_name  VARCHAR2(30)
  6. );

  7. grant execute on t_event_queue_payload to hr;

  8. BEGIN
  9.   -- Create a queue table to hold the event queue.
  10.   DBMS_AQADM.create_queue_table(
  11.     queue_table        => 'event_queue_tab',
  12.     queue_payload_type => 't_event_queue_payload',
  13.     multiple_consumers => TRUE,
  14.     comment            => 'Queue Table For Event Messages');
  15.   -- Create the event queue.
  16.   DBMS_AQADM.create_queue (
  17.     queue_name  => 'event_queue',
  18.     queue_table => 'event_queue_tab');
  19.   -- Start the event queue.
  20.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
  21. END;

  22. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE'
  23.   and q.QUEUE_TABLE=upper('event_queue_tab');

  24. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE'
  25.   and q.QUEUE_TABLE=upper('event_queue_tab');
复制代码
  1. begin
  2.    dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
  3. end;
  4.    
  5. begin
  6.    dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
  7. end;
复制代码
  1. grant execute on dbms_aq to hr;
复制代码
hr准备一些表和序列号来测试:
  1. CREATE TABLE scheduler_test (
  2.   id            NUMBER(10)    NOT NULL,
  3.   created_date  DATE          NOT NULL,
  4.   event_name  varchar2(40),
  5.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
  6. );

  7. CREATE SEQUENCE scheduler_test_seq;

  8. select  * from scheduler_test;
复制代码
做基于事件的schedule4:
  1.         
  2.                         
  3. BEGIN
  4. sys.dbms_scheduler.create_event_schedule(
  5. event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
  6. queue_spec => '"SYS"."EVENT_QUEUE"',
  7. start_date => systimestamp at time zone 'Asia/Shanghai',
  8. comments => 'schedule4',
  9. schedule_name => '"HR"."SCHEDULE4"');
  10. END;
复制代码
  1. create or replace procedure hr.procnew( p_1     sys.t_event_queue_payload    )
  2. is
  3. BEGIN
  4.      INSERT INTO hr.scheduler_test
  5.          VALUES (scheduler_test_seq.NEXTVAL, SYSDATE,  p_1.event_name   );
  6.          COMMIT;
  7. END;

  8. begin
  9.   dbms_scheduler.create_program (
  10.    program_name => 'hr.program4',
  11.     program_action=> 'hr.procnew',
  12.     program_type => 'STORED_PROCEDURE',
  13.     number_of_arguments => 1,
  14.     enabled => false) ;
  15. end;

  16. select  * from dba_scheduler_programs;
复制代码
   PROGRAM_NAMEPROGRAM_TYPEPROGRAM_ACTIONNUMBER_OF_ARGUMENTSENABLEDDETACHEDSCHEDULE_LIMITPRIORITYWEIGHTMAX_RUNSMAX_FAILURESMAX_RUN_DURATIONNLS_ENVCOMMENTS
1PROGRAM1PLSQL_BLOCKbegin
insert into t05317_1 values (to_char( sysdate ,'YYYY-MM-DD') );
end;
0TRUEFALSE 31 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE'PROGRAM1
2PROGRAM2EXECUTABLE/home/oracle/job2.sh0TRUEFALSE 31 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE'program2
3PROGRAM4STORED_PROCEDUREprocnew1FALSEFALSE 31 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE'

  1. begin
  2.    dbms_scheduler.define_metadata_argument (
  3.     program_name => 'hr.program4',
  4.     argument_position => 1 ,
  5.    metadata_attribute => 'EVENT_MESSAGE') ;
  6. end;

  7. select  * from dba_scheduler_program_args;
复制代码
   PROGRAM_NAMEARGUMENT_NAMEARGUMENT_POSITIONARGUMENT_TYPEMETADATA_ATTRIBUTEDEFAULT_VALUEOUT_ARGUMENT
1PROGRAM4 1 EVENT_MESSAGE<NO VALUE>FALSE

  1. begin
  2. dbms_scheduler.enable ('program4');
  3. end;
复制代码
b.png

job4基于事件,没有预期运行时间:
c.png

APPS打消息:
  1. DECLARE
  2.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
  3.   l_message_properties  DBMS_AQ.message_properties_t;
  4.   l_message_handle      RAW(16);
  5.   l_queue_msg           sys.t_event_queue_payload;
  6. BEGIN
  7.   l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');

  8.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
  9.                   enqueue_options     => l_enqueue_options,
  10.                   message_properties  => l_message_properties,
  11.                   payload             => l_queue_msg,
  12.                   msgid               => l_message_handle);
  13.   COMMIT;
  14. END;
复制代码
  1. select  * from scheduler_test;
复制代码
   IDCREATED_DATEEVENT_NAME
117/28/2018 6:11:26 PMgive_me_a_prod

如果乱传递消息:
e.png


作业状态电子邮件通知:
a.png

  1. BEGIN
  2. sys.dbms_scheduler.disable( '"HR"."JOB4"' );
  3. sys.dbms_scheduler.set_attribute( name => '"HR"."JOB4"', attribute => 'raise_events', value => dbms_scheduler.job_succeeded + dbms_scheduler.job_failed + dbms_scheduler.job_completed + dbms_scheduler.job_chain_stalled);
  4. sys.dbms_scheduler.enable( '"HR"."JOB4"' );
  5. END;
复制代码
  1. select  * from dba_scheduler_global_attribute;
复制代码
   ATTRIBUTE_NAMEVALUE
1MAX_JOB_SLAVE_PROCESSES
2LOG_HISTORY30
3DEFAULT_TIMEZONEPRC
4EMAIL_SERVER
5EMAIL_SERVER_ENCRYPTIONNONE
6EMAIL_SERVER_CREDENTIAL
7EMAIL_SENDER
8LAST_OBSERVED_EVENT720D479E75657B7AE0535A00A8C06CD6::94138::SCHED::0000
9EVENT_EXPIRY_TIME
10FILE_WATCHER_COUNT0
11CURRENT_OPEN_WINDOWSATURDAY_WINDOW

  1. begin
  2. DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
  3. ('email_server','station90.example.com:25');
  4. end;
复制代码
  1. [root@station90 ~]# service postfix start
  2. 启动 postfix:                                             [确定]
  3. [root@station90 ~]# service dovecot start
  4. 正在启动 Dovecot Imap:                                    [确定]
  5. [root@station90 ~]#

复制代码
main.cf (27 KB, 下载次数: 7)
回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2018-8-20 02:57 , Processed in 0.193479 second(s), 27 queries .

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