Bo's Oracle Station

查看: 2057|回复: 0

课程第26次(2018-08-14星期二)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-8-14 19:34:40 | 显示全部楼层 |阅读模式
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=20190607,20190608,20190701'
  6. ,comments => 'XXX 2019'
  7. );
  8. END;

  9. BEGIN
  10. /* Complex scheduling example 2: Working Days */
  11. DBMS_SCHEDULER.CREATE_SCHEDULE
  12. (schedule_name => 'HR.SCHEDULE31'
  13. ,repeat_interval =>
  14. 'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;'
  15. ,comments => 'Run on every working day'
  16. );
  17. END;


  18. declare
  19.    v_1  timestamp;
  20. begin
  21.   for i in 1..10
  22.    loop
  23.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (    calendar_string=>'FREQ=yearly;bydate=0607,0608,0701;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31'   ,
  24.    start_date => to_date('2015-01-01','YYYY-MM-DD'),
  25.    return_date_after  => v_1,
  26.    next_run_date    => v_1 );
  27.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
  28. end loop;
  29. end;


  30. BEGIN
  31. sys.dbms_scheduler.create_schedule(
  32. repeat_interval => 'FREQ=yearly;bydate=0607,0608,0701;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31',
  33. start_date => systimestamp at time zone 'Asia/Shanghai',
  34. schedule_name => '"HR"."SCHEDULE32"');
  35. END;

  36. declare
  37.    v_1  timestamp;
  38. begin
  39.   for i in 1..30
  40.    loop
  41.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (  
  42.           calendar_string=>'HR.SCHEDULE32'   ,
  43.    start_date => sysdate,
  44.    return_date_after  => v_1,
  45.    next_run_date    => v_1 );
  46.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
  47. end loop;
  48. end;

复制代码
4. 基于事件:
准备hr的表和SYS的管道:

  1. CREATE TABLE hr.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. select  * from hr.scheduler_test;

  8. CREATE SEQUENCE hr.scheduler_test_seq;

  9. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;
  10. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
  11. select  * from dba_queues q where q.QUEUE_TYPE   not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');

  12. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
  13.   event_name  VARCHAR2(30)
  14. );

  15. grant execute on t_event_queue_payload to hr;


  16. BEGIN
  17.   -- Create a queue table to hold the event queue.
  18.   DBMS_AQADM.create_queue_table(
  19.     queue_table        => 'event_queue_tab',
  20.     queue_payload_type => 't_event_queue_payload',
  21.     multiple_consumers => TRUE,
  22.     comment            => 'Queue Table For Event Messages');
  23.   -- Create the event queue.
  24.   DBMS_AQADM.create_queue (
  25.     queue_name  => 'event_queue',
  26.     queue_table => 'event_queue_tab');
  27.   -- Start the event queue.
  28.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
  29. END;

  30. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;
  31. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';


  32. begin
  33.    dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
  34. end;
  35.    
  36. begin
  37.    dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
  38. end;

复制代码
接下来,准备定义一句ADT文本“give me a prod”, 并创建基于事件的schedule:
1.png


  1. BEGIN
  2. sys.dbms_scheduler.create_event_schedule(
  3. event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
  4. queue_spec => '"SYS"."EVENT_QUEUE"',
  5. start_date => systimestamp at time zone 'Asia/Shanghai',
  6. schedule_name => '"HR"."SCHEDULE4"');
  7. END;
复制代码
再接下来,做program:
  1. create or replace procedure hr.procjob4   ( p_1     sys.t_event_queue_payload    )
  2. is
  3. BEGIN
  4.      INSERT INTO hr.scheduler_test
  5.          VALUES (hr.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.procjob4',
  12.     program_type => 'STORED_PROCEDURE',
  13.     number_of_arguments => 1,
  14.     enabled => false) ;
  15. end;


  16. begin
  17.    dbms_scheduler.define_metadata_argument (
  18.     program_name => 'hr.program4',
  19.     argument_position => 1 ,
  20.    metadata_attribute => 'EVENT_MESSAGE') ;
  21. end;


  22. begin
  23. dbms_scheduler.enable ('hr.program4');
  24. end;

复制代码
最后组装job4:
  1. BEGIN
  2. sys.dbms_scheduler.create_job(
  3. job_name => '"HR"."JOB4"',
  4. program_name => '"HR"."PROGRAM4"',
  5. schedule_name => '"HR"."SCHEDULE4"',
  6. job_class => '"DEFAULT_JOB_CLASS"',
  7. comments => 'job4',
  8. auto_drop => FALSE,
  9. enabled => TRUE);
  10. END;
复制代码

模拟单片机,打消息:
  1. SELECT * FROM hr.scheduler_test order by id;


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

  9.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
  10.                   enqueue_options     => l_enqueue_options,
  11.                   message_properties  => l_message_properties,
  12.                   payload             => l_queue_msg,
  13.                   msgid               => l_message_handle);
  14.   COMMIT;
  15. END;

复制代码

2.png


3.png

4.png
  1.         BEGIN
  2.     dbms_resource_manager.set_initial_consumer_group(
  3.         user => 'HR',
  4.         consumer_group => 'GROUP1'
  5.     );
  6. END;
复制代码


5.png


  1. select  s.USERNAME, s.RESOURCE_CONSUMER_GROUP
  2. from v_$session s
  3. where s.USERNAME='HR' and s.TERMINAL='pts/1';
复制代码
   USERNAMERESOURCE_CONSUMER_GROUP
1HRGROUP1

高级scheduler概念:
  1. BEGIN
  2. sys.dbms_scheduler.create_job_class(
  3. logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
  4. resource_consumer_group => 'GROUP2',
  5. comments => 'jobclass1',
  6. job_class_name => '"JOBCLASS1"');
  7. END;
复制代码
  1. grant execute on jobclass1 to hr;
复制代码
  1. BEGIN
  2. DBMS_SCHEDULER.CREATE_WINDOW(
  3. window_name=>'"WINDOW1"',
  4. resource_plan=>'PLAN1',
  5. start_date=>to_timestamp_tz('2018-08-14 21:30:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
  6. duration=>numtodsinterval(1, 'minute'),
  7. repeat_interval=>null,
  8. end_date=>null,
  9. window_priority=>'HIGH',
  10. comments=>'window1');
  11. END;
复制代码
  1. BEGIN
  2. DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
  3. group_name=>'"WINGROUP1"',
  4. window_list=>'"WINDOW1"');
  5. END;
复制代码
  1.         
  2.         [Return]
  3. BEGIN
  4. sys.dbms_scheduler.create_job(
  5. job_name => '"HR"."JOB7"',
  6. job_type => 'PLSQL_BLOCK',
  7. job_action => 'declare
  8.   v1  number;

  9. begin
  10.    select count(*)  into v1 from t_big a , t_big b;
  11. end;',
  12. schedule_name => '"SYS"."WINGROUP1"',
  13. job_class => '"JOBCLASS1"',
  14. auto_drop => FALSE,
  15. enabled => FALSE);
  16. sys.dbms_scheduler.set_attribute( name => '"HR"."JOB7"', attribute => 'stop_on_window_close', value => FALSE);
  17. sys.dbms_scheduler.enable( '"HR"."JOB7"' );
  18. END;
复制代码
  1. create table hr.t05317_lw( a timestamp ) ;

  2. create or replace procedure hr.proc05317_lw
  3. is
  4. v_1 number;
  5. begin
  6.    select count(*) into v_1 from employees a, employees b;
  7.    insert into  t05317_lw values(systimestamp);
  8.    commit;
  9. end;


  10. BEGIN
  11. DBMS_SCHEDULER.CREATE_PROGRAM(
  12. program_name=>'"HR"."PROGRAM8"',
  13. program_action=>'"HR"."PROC05317_LW"',
  14. program_type=>'STORED_PROCEDURE',
  15. number_of_arguments=>0,
  16. comments=>'',
  17. enabled=>TRUE);
  18. END;


  19. DECLARE
  20. newjob sys.job;
  21. newjobarr sys.job_array;
  22. BEGIN
  23. -- Create an array of JOB object types
  24.   newjobarr := sys.job_array();
  25. -- Allocate sufficient space in the array
  26.   newjobarr.extend(100);
  27. -- Add definitions for jobs
  28. FOR i IN 1..100 LOOP
  29.    -- Create a JOB object type
  30.    newjob := sys.job(job_name => 'HR.JOB8_' || to_char(i),
  31.                      job_style => 'LIGHTWEIGHT',
  32.                      job_template => 'HR.PROGRAM8',
  33.                    enabled => TRUE );
  34. -- Add job to the array
  35.    newjobarr(i) := newjob;
  36. END LOOP;
  37. -- Call CREATE_JOBS to create jobs in one transaction
  38. DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
  39. END;

  40. select count(*) from hr.t05317_lw;

  41. select max(a) - min(a) from hr.t05317_lw;
复制代码

SetupPreferences[url=]Help[/url]Logout
Logged in As SYS


Scheduler Jobs

Page RefreshedAug 14, 2018 9:48:50 PM CST
AllRunning

History

Previous
Next 25
SelectStatusNameSchemaCompletion DateRun Duration (minutes)
JOB8_95HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_96HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_91HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_9HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_93HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_94HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_92HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_98HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_99HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_90HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_97HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_89HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_88HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_87HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_86HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_8HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_73HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_70HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_7HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_77HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_78HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_69HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_81HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_79HRAug 14, 2018 9:47:01 PM +08:000.0
JOB8_68HRAug 14, 2018 9:47:01 PM +08:000.0
Previous
Next 25

AllRunning

History

Related Links
ChainsProgramsWindowsGlobal AttributesSchedulesJob ClassesWindow Groups






Database  |  Setup  |  Preferences  |  [url=]Help[/url]  |  Logout
Copyright © 1996, 2011, Oracle.  All rights reserved.
Oracle, JD Edwards, PeopleSoft, and Retek are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
About Oracle Enterprise Manager




回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 13:07 , Processed in 0.047058 second(s), 27 queries .

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