课程第26次（2018-08-14星期二）

botang

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. 
   
10. BEGIN
    
11. /* Complex scheduling example 2: Working Days */
    
12. DBMS_SCHEDULER.CREATE_SCHEDULE
    
13. (schedule_name => 'HR.SCHEDULE31'
    
14. ,repeat_interval =>
    
15. 'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;'
    
16. ,comments => 'Run on every working day'
    
17. );
    
18. END;
    
19. 
    
20. 
    
21. declare
    
22.    v_1  timestamp;
    
23. begin
    
24.   for i in 1..10
    
25.    loop
    
26.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (    calendar_string=>'FREQ=yearly;bydate=0607,0608,0701;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31'   ,
    
27.    start_date => to_date('2015-01-01','YYYY-MM-DD'),
    
28.    return_date_after  => v_1,
    
29.    next_run_date    => v_1 );
    
30.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
    
31. end loop;
    
32. end;
    
33. 
    
34. 
    
35. BEGIN
    
36. sys.dbms_scheduler.create_schedule(
    
37. repeat_interval => 'FREQ=yearly;bydate=0607,0608,0701;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31',
    
38. start_date => systimestamp at time zone 'Asia/Shanghai',
    
39. schedule_name => '"HR"."SCHEDULE32"');
    
40. END;
    
41. 
    
42. declare
    
43.    v_1  timestamp;
    
44. begin
    
45.   for i in 1..30
    
46.    loop
    
47.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (  
    
48.           calendar_string=>'HR.SCHEDULE32'   ,
    
49.    start_date => sysdate,
    
50.    return_date_after  => v_1,
    
51.    next_run_date    => v_1 );
    
52.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
    
53. end loop;
    
54. end;
    
55. 
    

复制代码

4. 基于事件：
准备hr的表和SYS的管道：

1. 
   
2. CREATE TABLE hr.scheduler_test (
   
3.   id            NUMBER(10)    NOT NULL,
   
4.   created_date  DATE          NOT NULL,
   
5.   event_name  varchar2(40),
   
6.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
   
7. );
   
8. 
   
9. 
   
10. select  * from hr.scheduler_test;
    
11. 
    
12. CREATE SEQUENCE hr.scheduler_test_seq;
    
13. 
    
14. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;
    
15. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
    
16. select  * from dba_queues q where q.QUEUE_TYPE   not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');
    
17. 
    
18. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
    
19.   event_name  VARCHAR2(30)
    
20. );
    
21. 
    
22. grant execute on t_event_queue_payload to hr;
    
23. 
    
24. 
    
25. BEGIN
    
26.   -- Create a queue table to hold the event queue.
    
27.   DBMS_AQADM.create_queue_table(
    
28.     queue_table        => 'event_queue_tab',
    
29.     queue_payload_type => 't_event_queue_payload',
    
30.     multiple_consumers => TRUE,
    
31.     comment            => 'Queue Table For Event Messages');
    
32.   -- Create the event queue.
    
33.   DBMS_AQADM.create_queue (
    
34.     queue_name  => 'event_queue',
    
35.     queue_table => 'event_queue_tab');
    
36.   -- Start the event queue.
    
37.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
    
38. END;
    
39. 
    
40. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;
    
41. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
    
42. 
    
43. 
    
44. begin
    
45.    dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
    
46. end;
    
47.    
    
48. begin
    
49.    dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
    
50. end;
    
51. 
    

复制代码

接下来，准备定义一句ADT文本“give me a prod”， 并创建基于事件的schedule：

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. 
   
9. 
   
10. begin
    
11.   dbms_scheduler.create_program (
    
12.    program_name => 'hr.program4',
    
13.     program_action=> 'hr.procjob4',
    
14.     program_type => 'STORED_PROCEDURE',
    
15.     number_of_arguments => 1,
    
16.     enabled => false) ;
    
17. end;
    
18. 
    
19. 
    
20. begin
    
21.    dbms_scheduler.define_metadata_argument (
    
22.     program_name => 'hr.program4',
    
23.     argument_position => 1 ,
    
24.    metadata_attribute => 'EVENT_MESSAGE') ;
    
25. end;
    
26. 
    
27. 
    
28. begin
    
29. dbms_scheduler.enable ('hr.program4');
    
30. end;
    
31. 
    

复制代码

最后组装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. 
   
3. 
   
4. DECLARE
   
5.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
   
6.   l_message_properties  DBMS_AQ.message_properties_t;
   
7.   l_message_handle      RAW(16);
   
8.   l_queue_msg           sys.t_event_queue_payload;
   
9. BEGIN
   
10.   l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');
    
11. 
    
12.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
    
13.                   enqueue_options     => l_enqueue_options,
    
14.                   message_properties  => l_message_properties,
    
15.                   payload             => l_queue_msg,
    
16.                   msgid               => l_message_handle);
    
17.   COMMIT;
    
18. END;
    
19. 
    

复制代码

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

复制代码

1. select  s.USERNAME, s.RESOURCE_CONSUMER_GROUP
   
2. from v_$session s
   
3. where s.USERNAME='HR' and s.TERMINAL='pts/1';

复制代码

	USERNAME	RESOURCE_CONSUMER_GROUP
1	HR	GROUP1

高级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. 
   
10. begin
    
11.    select count(*)  into v1 from t_big a , t_big b;
    
12. end;',
    
13. schedule_name => '"SYS"."WINGROUP1"',
    
14. job_class => '"JOBCLASS1"',
    
15. auto_drop => FALSE,
    
16. enabled => FALSE);
    
17. sys.dbms_scheduler.set_attribute( name => '"HR"."JOB7"', attribute => 'stop_on_window_close', value => FALSE);
    
18. sys.dbms_scheduler.enable( '"HR"."JOB7"' );
    
19. END;

复制代码

1. create table hr.t05317_lw( a timestamp ) ;
   
2. 
   
3. create or replace procedure hr.proc05317_lw
   
4. is
   
5. v_1 number;
   
6. begin
   
7.    select count(*) into v_1 from employees a, employees b;
   
8.    insert into  t05317_lw values(systimestamp);
   
9.    commit;
   
10. end;
    
11. 
    
12. 
    
13. BEGIN
    
14. DBMS_SCHEDULER.CREATE_PROGRAM(
    
15. program_name=>'"HR"."PROGRAM8"',
    
16. program_action=>'"HR"."PROC05317_LW"',
    
17. program_type=>'STORED_PROCEDURE',
    
18. number_of_arguments=>0,
    
19. comments=>'',
    
20. enabled=>TRUE);
    
21. END;
    
22. 
    
23. 
    
24. DECLARE
    
25. newjob sys.job;
    
26. newjobarr sys.job_array;
    
27. BEGIN
    
28. -- Create an array of JOB object types
    
29.   newjobarr := sys.job_array();
    
30. -- Allocate sufficient space in the array
    
31.   newjobarr.extend(100);
    
32. -- Add definitions for jobs
    
33. FOR i IN 1..100 LOOP
    
34.    -- Create a JOB object type
    
35.    newjob := sys.job(job_name => 'HR.JOB8_' || to_char(i),
    
36.                      job_style => 'LIGHTWEIGHT',
    
37.                      job_template => 'HR.PROGRAM8',
    
38.                    enabled => TRUE );
    
39. -- Add job to the array
    
40.    newjobarr(i) := newjob;
    
41. END LOOP;
    
42. -- Call CREATE_JOBS to create jobs in one transaction
    
43. DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
    
44. END;
    
45. 
    
46. select count(*) from hr.t05317_lw;
    
47. 
    
48. select max(a) - min(a) from hr.t05317_lw;
    

复制代码

Setup Preferences [url=]Help[/url] Logout

Database Instance: orcl.example.com  >

Logged in As SYS

Scheduler Jobs

Page Refreshed Aug 14, 2018 9:48:50 PM CST

	All	Running		History

Previous Next 25 Select Status Name Schema Completion Date Run Duration (minutes) JOB8_95 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_96 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_91 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_9 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_93 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_94 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_92 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_98 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_99 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_90 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_97 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_89 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_88 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_87 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_86 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_8 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_73 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_70 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_7 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_77 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_78 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_69 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_81 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_79 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 JOB8_68 HR Aug 14, 2018 9:47:01 PM +08:00 0.0 Previous Next 25

	All	Running		History

	Related Links
	ChainsProgramsWindows Global AttributesSchedules Job 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