活动第45/46次：scheduler（2018-08-04星期六上下午）

botang

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. 
   
3. 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.    

复制代码

	OWNER	CREDENTIAL_NAME	USERNAME	DATABASE_ROLE	WINDOWS_DOMAIN	COMMENTS
1	SYS	CREDENTIAL1	oracle

1. grant execute on credential1 to hr;

复制代码

写一个shell脚本：

1. #!/bin/sh
   
2. 
   
3. export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
   
4. export ORACLE_SID=orcl
   
5. export ORACLE_BASE=/u01/app/oracle
   
6. 
   
7. /u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus /nolog >> /home/oracle/job2a.log <<EOF
   
8. conn hr/oracle_4U
   
9. alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
   
10. select  sysdate from dual;
    
11. exit
    
12. EOF
    
13. 
    
14. 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;

复制代码

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. 
    
16. 
    
17. begin
    
18.    dbms_scheduler.create_schedule(schedule_name => 'HR.SCHEDULE32',
    
19.    repeat_interval => 'FREQ=yearly;bydate=0701,0801,1001;BYHOUR=14;
    
20.                     BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31' );
    
21. end;
    
22.    
    

复制代码

-----------------------
4. 为了做基于事件的schedule，需要先制造queue：

1. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;
   
2. 
   
3. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
   
4. 
   
5. select  * from dba_queues q where q.QUEUE_TYPE   not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');
   
6. 
   
7. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
   
8.   event_name  VARCHAR2(30)
   
9. );
   
10. 
    
11. grant execute on t_event_queue_payload to hr;
    
12. 
    
13. BEGIN
    
14.   -- Create a queue table to hold the event queue.
    
15.   DBMS_AQADM.create_queue_table(
    
16.     queue_table        => 'event_queue_tab',
    
17.     queue_payload_type => 't_event_queue_payload',
    
18.     multiple_consumers => TRUE,
    
19.     comment            => 'Queue Table For Event Messages');
    
20.   -- Create the event queue.
    
21.   DBMS_AQADM.create_queue (
    
22.     queue_name  => 'event_queue',
    
23.     queue_table => 'event_queue_tab');
    
24.   -- Start the event queue.
    
25.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
    
26. END;
    
27. 
    
28. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE'
    
29.   and q.QUEUE_TABLE=upper('event_queue_tab');
    
30. 
    
31. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE'
    
32.   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. 
   
8. CREATE SEQUENCE scheduler_test_seq;
   
9. 
   
10. 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. 
   
9. begin
   
10.   dbms_scheduler.create_program (
    
11.    program_name => 'hr.program4',
    
12.     program_action=> 'hr.procnew',
    
13.     program_type => 'STORED_PROCEDURE',
    
14.     number_of_arguments => 1,
    
15.     enabled => false) ;
    
16. end;
    
17. 
    
18. select  * from dba_scheduler_programs;
    

复制代码

PROGRAM_NAME

PROGRAM_TYPE

PROGRAM_ACTION

NUMBER_OF_ARGUMENTS

ENABLED

DETACHED

SCHEDULE_LIMIT

PRIORITY

WEIGHT

MAX_RUNS

MAX_FAILURES

MAX_RUN_DURATION

NLS_ENV

COMMENTS

1

PROGRAM1

PLSQL_BLOCK

begin insert into t05317_1 values (to_char( sysdate ,'YYYY-MM-DD') ); end;

0

TRUE

FALSE

3

1

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

2

PROGRAM2

EXECUTABLE

/home/oracle/job2.sh

0

TRUE

FALSE

3

1

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

3

PROGRAM4

STORED_PROCEDURE

procnew

1

FALSE

FALSE

3

1

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. 
   
8. select  * from dba_scheduler_program_args;

复制代码

	PROGRAM_NAME	ARGUMENT_NAME	ARGUMENT_POSITION	ARGUMENT_TYPE	METADATA_ATTRIBUTE	DEFAULT_VALUE	OUT_ARGUMENT
1	PROGRAM4		1		EVENT_MESSAGE	<NO VALUE>	FALSE

1. begin
   
2. dbms_scheduler.enable ('program4');
   
3. end;

复制代码

job4基于事件，没有预期运行时间：


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. 
   
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;

复制代码

1. select  * from scheduler_test;

复制代码

	ID	CREATED_DATE	EVENT_NAME
1	1	7/28/2018 6:11:26 PM	give_me_a_prod

如果乱传递消息：



作业状态电子邮件通知：

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_NAME	VALUE
1	MAX_JOB_SLAVE_PROCESSES
2	LOG_HISTORY	30
3	DEFAULT_TIMEZONE	PRC
4	EMAIL_SERVER
5	EMAIL_SERVER_ENCRYPTION	NONE
6	EMAIL_SERVER_CREDENTIAL
7	EMAIL_SENDER
8	LAST_OBSERVED_EVENT	720D479E75657B7AE0535A00A8C06CD6::94138::SCHED::0000
9	EVENT_EXPIRY_TIME
10	FILE_WATCHER_COUNT	0
11	CURRENT_OPEN_WINDOW	SATURDAY_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 ~]#
   
6. 
   

复制代码

main.cf (27 KB, 下载次数: 375)

2018-8-4 09:35 上传

点击文件名下载附件
dovecot.conf (3.39 KB, 下载次数: 372)

2018-8-4 09:35 上传

点击文件名下载附件 10-mail.conf (14.11 KB, 下载次数: 366)

2018-8-4 09:35 上传

点击文件名下载附件

1. [root@station90 桌面]# dig example.com
   
2. 
   
3. ; <<>> DiG 9.7.3-RedHat-9.7.3-2.el6 <<>> example.com
   
4. ;; global options: +cmd
   
5. ;; Got answer:
   
6. ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 59588
   
7. ;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1
   
8. 
   
9. ;; QUESTION SECTION:
   
10. ;example.com.                        IN        A
    
11. 
    
12. ;; ANSWER SECTION:
    
13. example.com.                86400        IN        A        192.168.0.90
    
14. 
    
15. ;; AUTHORITY SECTION:
    
16. example.com.                86400        IN        NS        station90.example.com.
    
17. 
    
18. ;; ADDITIONAL SECTION:
    
19. station90.example.com.        86400        IN        A        192.168.0.90
    
20. 
    
21. ;; Query time: 0 msec
    
22. ;; SERVER: 192.168.0.90#53(192.168.0.90)
    
23. ;; WHEN: Sat Aug  4 09:36:48 2018
    
24. ;; MSG SIZE  rcvd: 85
    
25. 
    
26. [root@station90 桌面]# id oracle
    
27. uid=500(oracle) gid=500(oinstall) 组=500(oinstall),0(root),501(dba),6005(oper),6006(asmadmin),6007(asmdba),6008(asmoper)
    
28. [root@station90 桌面]#
    

复制代码

1. select  * from dba_scheduler_global_attribute;
   
2. 
   
3. begin
   
4. DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
   
5. ('email_server','station90.example.com:25');
   
6. end;
   
7. 
   
8. begin
   
9.   DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
   
10.   ('email_sender','oracle@example.com');
    
11. end;
    
12. 
    
13. 
    
14. select  * from dba_scheduler_credentials;
    
15. 
    
16. begin
    
17.      dbms_scheduler.create_credential(credential_name => 'CREDENTIAL2',
    
18.                                                              username =>'oracle',
    
19.                                                              password => 'oracle');
    
20.   end;
    
21.   
    
22.   begin
    
23.      dbms_scheduler.set_scheduler_attribute(attribute => 'EMAIL_SERVER_CREDENTIAL',
    
24.      value => 'CREDENTIAL2');
    
25.    end;
    
26.    
    
27.    grant execute on credential2 to hr;
    
28.      
    
29.   select  * from dba_scheduler_notifications;
    
30.   
    
31.   begin
    
32.   DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    
33.   job_name       =>'HR.JOB4',
    
34.     events   =>'job_succeeded,job_completed',
    
35.   recipients     =>'oracle@example.com');
    
36. end;
    
37.                                                               
    
38.       
    
39.   select  * from dba_scheduler_notifications;
    
40.   
    
41.       DECLARE
    
42.       l_enqueue_options     DBMS_AQ.enqueue_options_t;
    
43.       l_message_properties  DBMS_AQ.message_properties_t;
    
44.       l_message_handle      RAW(16);
    
45.       l_queue_msg           sys.t_event_queue_payload;
    
46.     BEGIN
    
47.       l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');
    
48. 
    
49.       DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
    
50.                       enqueue_options     => l_enqueue_options,
    
51.                       message_properties  => l_message_properties,
    
52.                       payload             => l_queue_msg,
    
53.                       msgid               => l_message_handle);
    
54.       COMMIT;
    
55.     END;
    
56.   
    
57.   select  * from hr.scheduler_test;

复制代码

1. BEGIN
   
2. dbms_resource_manager.clear_pending_area();
   
3. dbms_resource_manager.create_pending_area();
   
4. dbms_resource_manager.create_consumer_group(consumer_group => 'group1',comment => 'group1' , cpu_mth => 'ROUND-ROBIN');
   
5. dbms_resource_manager.submit_pending_area();
   
6. BEGIN
   
7. dbms_resource_manager_privs.grant_switch_consumer_group('HR','group1',case 'false' when 'false' then false when 'true' then true else false end);
   
8. END;END;

复制代码

1.         
   
2.                         
   
3. BEGIN
   
4. dbms_resource_manager.clear_pending_area();
   
5. dbms_resource_manager.create_pending_area();
   
6. dbms_resource_manager.create_consumer_group(consumer_group => 'group2',comment => 'group2' , cpu_mth => 'ROUND-ROBIN');
   
7. dbms_resource_manager.submit_pending_area();
   
8. BEGIN
   
9. dbms_resource_manager_privs.grant_switch_consumer_group('HR','group2',case 'false' when 'false' then false when 'true' then true else false end);
   
10. END;END;

复制代码

1. DECLARE
   
2. spfileValue VARCHAR2(1000);
   
3. execText VARCHAR2(1000);
   
4. scopeValue VARCHAR2(30) := 'MEMORY';
   
5. planName VARCHAR2(100) :='PLAN1';
   
6. BEGIN
   
7. dbms_resource_manager.clear_pending_area();
   
8. dbms_resource_manager.create_pending_area();
   
9. dbms_resource_manager.create_plan( plan => 'PLAN1', comment => 'plan1',max_iops => NULL,max_mbps => NULL );
   
10. dbms_resource_manager.create_plan_directive(
    
11.     plan => 'PLAN1',
    
12.     group_or_subplan => 'GROUP1',
    
13.     comment => '',
    
14.     switch_elapsed_time => NULL,
    
15.      max_utilization_limit => 50,
    
16.     mgmt_p1 => 20, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
    
17.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
18.     parallel_degree_limit_p1 => NULL ,
    
19.     parallel_target_percentage => NULL ,
    
20.     parallel_queue_timeout => NULL ,
    
21.     parallel_stmt_critical => 'false' ,
    
22.     switch_io_logical => NULL ,
    
23.     switch_io_reqs => NULL ,
    
24.    switch_io_megabytes => NULL ,
    
25.     active_sess_pool_p1 => NULL,
    
26.     queueing_p1 => NULL,
    
27.     switch_group => '',
    
28.     switch_time => NULL,
    
29.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
    
30.     undo_pool => NULL ,
    
31.     max_idle_time => NULL,
    
32.     max_idle_blocker_time => NULL,
    
33.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
34. 
    
35. );
    
36. dbms_resource_manager.create_plan_directive(
    
37.     plan => 'PLAN1',
    
38.     group_or_subplan => 'GROUP2',
    
39.     comment => '',
    
40.     switch_elapsed_time => NULL,
    
41.      max_utilization_limit => 8,
    
42.     mgmt_p1 => 8, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
    
43.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
44.     parallel_degree_limit_p1 => NULL ,
    
45.     parallel_target_percentage => NULL ,
    
46.     parallel_queue_timeout => NULL ,
    
47.     parallel_stmt_critical => 'false' ,
    
48.     switch_io_logical => NULL ,
    
49.     switch_io_reqs => NULL ,
    
50.    switch_io_megabytes => NULL ,
    
51.     active_sess_pool_p1 => NULL,
    
52.     queueing_p1 => NULL,
    
53.     switch_group => '',
    
54.     switch_time => NULL,
    
55.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
    
56.     undo_pool => NULL ,
    
57.     max_idle_time => NULL,
    
58.     max_idle_blocker_time => NULL,
    
59.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
60. 
    
61. );
    
62. dbms_resource_manager.create_plan_directive(
    
63.     plan => 'PLAN1',
    
64.     group_or_subplan => 'OTHER_GROUPS',
    
65.     comment => '',
    
66.     switch_elapsed_time => NULL,
    
67.      max_utilization_limit => 30,
    
68.     mgmt_p1 => 2, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
    
69.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
70.     parallel_degree_limit_p1 => NULL ,
    
71.     parallel_target_percentage => NULL ,
    
72.     parallel_queue_timeout => NULL ,
    
73.     parallel_stmt_critical => 'false' ,
    
74.     switch_io_logical => NULL ,
    
75.     switch_io_reqs => NULL ,
    
76.    switch_io_megabytes => NULL ,
    
77.     active_sess_pool_p1 => NULL,
    
78.     queueing_p1 => NULL,
    
79.     switch_group => '',
    
80.     switch_time => NULL,
    
81.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
    
82.     undo_pool => NULL ,
    
83.     max_idle_time => NULL,
    
84.     max_idle_blocker_time => NULL,
    
85.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
86. 
    
87. );
    
88. dbms_resource_manager.create_plan_directive(
    
89.     plan => 'PLAN1',
    
90.     group_or_subplan => 'SYS_GROUP',
    
91.     comment => '',
    
92.     switch_elapsed_time => NULL,
    
93.      max_utilization_limit => 100,
    
94.     mgmt_p1 => 70, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
    
95.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
96.     parallel_degree_limit_p1 => NULL ,
    
97.     parallel_target_percentage => NULL ,
    
98.     parallel_queue_timeout => NULL ,
    
99.     parallel_stmt_critical => 'false' ,
    
100.     switch_io_logical => NULL ,
     
101.     switch_io_reqs => NULL ,
     
102.    switch_io_megabytes => NULL ,
     
103.     active_sess_pool_p1 => NULL,
     
104.     queueing_p1 => NULL,
     
105.     switch_group => '',
     
106.     switch_time => NULL,
     
107.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
     
108.     undo_pool => NULL ,
     
109.     max_idle_time => NULL,
     
110.     max_idle_blocker_time => NULL,
     
111.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
112. 
     
113. );
     
114. dbms_resource_manager.submit_pending_area();
     
115. select value into spfileValue from v$parameter where name = 'spfile';
     
116. IF spfileValue IS NOT NULL then
     
117. EXECUTE IMMEDIATE 'alter system set resource_manager_plan = '||planName||' scope=BOTH';
     
118. END IF;
     
119. dbms_resource_manager.switch_plan( plan_name => 'PLAN1' , sid => 'orcl' );
     
120. END;
     
121.                         
     

复制代码

1. select  u.username, u.initial_rsrc_consumer_group
   
2.   from dba_users u  where u.username='HR' ;

复制代码

	USERNAME	INITIAL_RSRC_CONSUMER_GROUP
1	HR	DEFAULT_CONSUMER_GROUP

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

复制代码

	SID	USERNAME	RESOURCE_CONSUMER_GROUP
1	1495	HR	OTHER_GROUPS

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

复制代码

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> show parameter resource
   
4. 
   
5. NAME                                     TYPE         VALUE
   
6. ------------------------------------ ----------- ------------------------------
   
7. resource_limit                             boolean         TRUE
   
8. resource_manager_cpu_allocation      integer         4
   
9. resource_manager_plan                     string         PLAN1
   
10. SQL> conn  hr/oracle_4U
    
11. Connected.
    
12. SQL> !ps
    
13.   PID TTY          TIME CMD
    
14. 26430 pts/52   00:00:00 sqlplus
    
15. 26662 pts/52   00:00:00 ps
    
16. 
    
17. SQL> conn  hr/oracle_4U
    
18. Connected.
    
19. SQL>
    

复制代码

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

复制代码

1. 
   
2.            SID        USERNAME        RESOURCE_CONSUMER_GROUP
   
3. 
   
4. 1        511        HR        GROUP1
   

复制代码

前台所用的时间：
SQL> conn hr/oracle_4U
Connected.
SQL> select  count(*)  from t_big a  ,   departments b  , departments c  ;

  COUNT(*)
----------
238027977

Elapsed: 00:00:10.23
SQL> !ps
  PID TTY          TIME CMD
26430 pts/52   00:00:00 sqlplus
31726 pts/52   00:00:00 ps


作业类与资源组相绑定：

1. 
   
2. BEGIN
   
3. sys.dbms_scheduler.create_job_class(
   
4. logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
   
5. resource_consumer_group => 'GROUP2',
   
6. comments => 'jobclass1',
   
7. job_class_name => '"JOBCLASS1"');
   
8. END;

复制代码

1. grant execute on jobclass1 to hr;

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"HR"."JOB6"',
   
4. job_type => 'PLSQL_BLOCK',
   
5. job_action => 'declare
   
6.   v1 number;
   
7. begin
   
8.   select  count(*)  into v1  from t_big a  ,   departments b  , departments c  ;
   
9. end;',
   
10. start_date => systimestamp at time zone 'PRC',
    
11. job_class => '"JOBCLASS1"',
    
12. comments => 'job6',
    
13. auto_drop => TRUE,
    
14. enabled => TRUE);
    
15. END;

复制代码

group1 10秒  group2  1分钟  1：6
8%：50%    约 1：6

-------------------------------------

1.         
   
2.                         
   
3. BEGIN
   
4. DBMS_SCHEDULER.CREATE_WINDOW(
   
5. window_name=>'"WINDOW1"',
   
6. resource_plan=>'PLAN1',
   
7. start_date=>to_timestamp_tz('2018-08-04 11:55:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
   
8. duration=>numtodsinterval(1, 'minute'),
   
9. repeat_interval=>null,
   
10. end_date=>null,
    
11. window_priority=>'HIGH',
    
12. comments=>'WINDOW1');
    
13. END;

复制代码

1.         
   
2.                         
   
3. BEGIN
   
4. DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
   
5. group_name=>'"WINGROUP1"',
   
6. window_list=>'"WINDOW1"');
   
7. END;
   

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"HR"."JOB7"',
   
4. program_name => '"HR"."PROGRAM7"',
   
5. schedule_name => '"SYS"."WINGROUP1"',
   
6. job_class => '"JOBCLASS1"',
   
7. comments => 'job7',
   
8. auto_drop => TRUE,
   
9. enabled => FALSE);
   
10. sys.dbms_scheduler.set_attribute( name => '"HR"."JOB7"', attribute => 'stop_on_window_close', value => TRUE);
    
11. sys.dbms_scheduler.enable( '"HR"."JOB7"' );
    
12. END;

复制代码

1. SQL> show parameter resource
   
2. 
   
3. NAME                                     TYPE         VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. resource_limit                             boolean         TRUE
   
6. resource_manager_cpu_allocation      integer         4
   
7. resource_manager_plan                     string         SCHEDULER[0x17099]:PLAN1
   

复制代码

一分钟过后：

1. SQL> show parameter resource
   
2. 
   
3. NAME                                     TYPE         VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. resource_limit                             boolean         TRUE
   
6. resource_manager_cpu_allocation      integer         4
   
7. resource_manager_plan                     string         DEFAULT_PLAN
   
8. SQL>
   

复制代码

作业阵列和持续性轻量级作业：

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

复制代码

1. begin
   
2.    dbms_scheduler.create_job(
   
3.    job_name => 'hr.job8',
   
4.    program_name=>'hr.program8',
   
5.    schedule_name=>'hr.schedule8',
   
6.    job_style=>'LIGHTWEIGHT');
   
7. end;

复制代码

1. select  j.job_name,j.JOB_STYLE
   
2. from user_scheduler_jobs j ;

复制代码

	JOB_NAME	JOB_STYLE
1	JOB2	REGULAR
2	JOB7	REGULAR
3	JOB8	LIGHTWEIGHT

1. DECLARE
   
2. newjob sys.job;
   
3. newjobarr sys.job_array;
   
4. BEGIN
   
5. -- Create an array of JOB object types
   
6.   newjobarr := sys.job_array();
   
7. -- Allocate sufficient space in the array
   
8.   newjobarr.extend(100);
   
9. -- Add definitions for jobs
   
10. FOR i IN 1..100 LOOP
    
11.    -- Create a JOB object type
    
12.    newjob := sys.job(job_name => 'HR.JOB8_' || to_char(i),
    
13.                      job_style => 'LIGHTWEIGHT',
    
14.                      job_template => 'HR.PROGRAM8',
    
15.                    enabled => TRUE );
    
16. -- Add job to the array
    
17.    newjobarr(i) := newjob;
    
18. END LOOP;
    
19. -- Call CREATE_JOBS to create jobs in one transaction
    
20. DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
    
21. END;
    

复制代码

1. select max(a) - min(a) from t05317_lw;

复制代码

	MAX(A)-MIN(A)
1	+000000000 00:00:20.621948

1. DECLARE
   
2. newjob sys.job;
   
3. newjobarr sys.job_array;
   
4. BEGIN
   
5. -- Create an array of JOB object types
   
6.   newjobarr := sys.job_array();
   
7. -- Allocate sufficient space in the array
   
8.   newjobarr.extend(100);
   
9. -- Add definitions for jobs
   
10. FOR i IN 1..100 LOOP
    
11.    -- Create a JOB object type
    
12.    newjob := sys.job(job_name => 'HR.JOB80_' || to_char(i),
    
13.                      job_style => 'REGULAR',
    
14.                      job_template => 'HR.PROGRAM8',
    
15.                    enabled => TRUE );
    
16. -- Add job to the array
    
17.    newjobarr(i) := newjob;
    
18. END LOOP;
    
19. -- Call CREATE_JOBS to create jobs in one transaction
    
20. DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
    
21. END;

复制代码

1. select max(a) - min(a) from t05317_lw;

复制代码

	MAX(A)-MIN(A)
1	+000000000 00:00:23.043365