活动第45/46次：scheduler（2018-08-04星期六上下午）
1. 基于时间的schedule：BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
start_date => systimestamp at time zone 'Asia/Shanghai',
end_date => to_timestamp_tz('2018-07-28 16:35:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
comments => 'schedule1',
schedule_name => '"HR"."SCHEDULE1"');
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。）
grant create job to hr;
grant create external job to hr;
begin
dbms_scheduler.create_credential(credential_name =>'credential1',
username => 'oracle',
password => 'oracle');
end;
OWNERCREDENTIAL_NAMEUSERNAMEDATABASE_ROLEWINDOWS_DOMAINCOMMENTS
1SYSCREDENTIAL1oracle
grant execute on credential1 to hr;写一个shell脚本：
#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus /nolog >> /home/oracle/job2a.log <<EOF
conn hr/oracle_4U
alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
selectsysdate from dual;
exit
EOF
date +%Y%m%d-%H%M%S >> /home/oracle/job2b.log
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'"HR"."PROGRAM2"',
program_action=>'/home/oracle/job2.sh',
program_type=>'EXECUTABLE',
number_of_arguments=>0,
comments=>'program2',
enabled=>TRUE);
END;
跟schedule1类似：
BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
start_date => systimestamp at time zone 'Asia/Shanghai',
end_date => to_timestamp_tz('2018-07-28 16:52:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
comments => 'schedule2',
schedule_name => '"HR"."SCHEDULE2"');
END;
3. 基于日历的schedule：
BEGIN
/* Complex scheduling example 1: Public Holidays */
DBMS_SCHEDULER.CREATE_SCHEDULE
(schedule_name => 'HR.SCHEDULE3',
repeat_interval =>'freq=YEARLY;bydate=0701,0801,1001'
,comments => 'Politics Days'
);
END;
BEGIN
/* Complex scheduling example 2: Working Days */
DBMS_SCHEDULER.CREATE_SCHEDULE
(schedule_name => 'HR.SCHEDULE31',
repeat_interval =>'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;'
,comments => 'Run on every working day'
);
END;
'FREQ=YEARLY;BYYEARDAY=1,32,-306'
The following example creates a schedule consisting of the first working day of every month in 2008:
'freq=MONTHLY;byday=MON,TUE,WED,THU,FRI;bysetpos=1'
1st and 3rd Monday each month:
'freq=MONTHLY;byday=1MON,3MON'
The following example creates a schedule consisting of the last day of each quarter:
'freq=YEARLY;bymonth=MAR,JUN,SEP,DEC;bymonthday=-1'
declare
v_1timestamp;
begin
for i in 1..3
loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
calendar_string=>'FREQ=yearly;bydate=0701,0801,1001;BYHOUR=14;
BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31' ,
start_date => sysdate,
return_date_after=> v_1,
next_run_date => v_1 );
dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
end loop;
end;
begin
dbms_scheduler.create_schedule(schedule_name => 'HR.SCHEDULE32',
repeat_interval => 'FREQ=yearly;bydate=0701,0801,1001;BYHOUR=14;
BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31' );
end;
-----------------------
4. 为了做基于事件的schedule，需要先制造queue：
select* from dba_queues qwhereq.QUEUE_TYPE='NORMAL_QUEUE' ;
select* from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
select* from dba_queues q where q.QUEUE_TYPE not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');
CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
event_nameVARCHAR2(30)
);
grant execute on t_event_queue_payload to hr;
BEGIN
-- Create a queue table to hold the event queue.
DBMS_AQADM.create_queue_table(
queue_table => 'event_queue_tab',
queue_payload_type => 't_event_queue_payload',
multiple_consumers => TRUE,
comment => 'Queue Table For Event Messages');
-- Create the event queue.
DBMS_AQADM.create_queue (
queue_name=> 'event_queue',
queue_table => 'event_queue_tab');
-- Start the event queue.
DBMS_AQADM.start_queue (queue_name => 'event_queue');
END;
select* from dba_queues qwhereq.QUEUE_TYPE='NORMAL_QUEUE'
and q.QUEUE_TABLE=upper('event_queue_tab');
select* from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE'
and q.QUEUE_TABLE=upper('event_queue_tab');
begin
dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
end;
begin
dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
end;
grant execute on dbms_aq to hr;hr准备一些表和序列号来测试：
CREATE TABLE scheduler_test (
id NUMBER(10) NOT NULL,
created_dateDATE NOT NULL,
event_namevarchar2(40),
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
CREATE SEQUENCE scheduler_test_seq;
select* from scheduler_test;做基于事件的schedule4：
BEGIN
sys.dbms_scheduler.create_event_schedule(
event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
queue_spec => '"SYS"."EVENT_QUEUE"',
start_date => systimestamp at time zone 'Asia/Shanghai',
comments => 'schedule4',
schedule_name => '"HR"."SCHEDULE4"');
END;
create or replace procedure hr.procnew( p_1 sys.t_event_queue_payload )
is
BEGIN
INSERT INTO hr.scheduler_test
VALUES (scheduler_test_seq.NEXTVAL, SYSDATE,p_1.event_name );
COMMIT;
END;
begin
dbms_scheduler.create_program (
program_name => 'hr.program4',
program_action=> 'hr.procnew',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
enabled => false) ;
end;
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'
begin
dbms_scheduler.define_metadata_argument (
program_name => 'hr.program4',
argument_position => 1 ,
metadata_attribute => 'EVENT_MESSAGE') ;
end;
select* from dba_scheduler_program_args;
PROGRAM_NAMEARGUMENT_NAMEARGUMENT_POSITIONARGUMENT_TYPEMETADATA_ATTRIBUTEDEFAULT_VALUEOUT_ARGUMENT
1PROGRAM4 1 EVENT_MESSAGE<NO VALUE>FALSE
begin
dbms_scheduler.enable ('program4');
end;
job4基于事件，没有预期运行时间：
APPS打消息：
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_propertiesDBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg sys.t_event_queue_payload;
BEGIN
l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'sys.event_queue',
enqueue_options => l_enqueue_options,
message_properties=> l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
select* from scheduler_test;
IDCREATED_DATEEVENT_NAME
117/28/2018 6:11:26 PMgive_me_a_prod
如果乱传递消息：
作业状态电子邮件通知：
BEGIN
sys.dbms_scheduler.disable( '"HR"."JOB4"' );
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);
sys.dbms_scheduler.enable( '"HR"."JOB4"' );
END;
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
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_server','station90.example.com:25');
end;
# service postfix start
启动 postfix： [确定]
# service dovecot start
正在启动 Dovecot Imap： [确定]
#
# dig example.com
; <<>> DiG 9.7.3-RedHat-9.7.3-2.el6 <<>> example.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 59588
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1
;; QUESTION SECTION:
;example.com. IN A
;; ANSWER SECTION:
example.com. 86400 IN A 192.168.0.90
;; AUTHORITY SECTION:
example.com. 86400 IN NS station90.example.com.
;; ADDITIONAL SECTION:
station90.example.com. 86400 IN A 192.168.0.90
;; Query time: 0 msec
;; SERVER: 192.168.0.90#53(192.168.0.90)
;; WHEN: Sat Aug4 09:36:48 2018
;; MSG SIZErcvd: 85
# id oracle
uid=500(oracle) gid=500(oinstall) 组=500(oinstall),0(root),501(dba),6005(oper),6006(asmadmin),6007(asmdba),6008(asmoper)
#
select* from dba_scheduler_global_attribute;
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_server','station90.example.com:25');
end;
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_sender','oracle@example.com');
end;
select* from dba_scheduler_credentials;
begin
dbms_scheduler.create_credential(credential_name => 'CREDENTIAL2',
username =>'oracle',
password => 'oracle');
end;
begin
dbms_scheduler.set_scheduler_attribute(attribute => 'EMAIL_SERVER_CREDENTIAL',
value => 'CREDENTIAL2');
end;
grant execute on credential2 to hr;
select* from dba_scheduler_notifications;
begin
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB4',
events =>'job_succeeded,job_completed',
recipients =>'oracle@example.com');
end;
select* from dba_scheduler_notifications;
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_propertiesDBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg sys.t_event_queue_payload;
BEGIN
l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'sys.event_queue',
enqueue_options => l_enqueue_options,
message_properties=> l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
select* from hr.scheduler_test;
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group(consumer_group => 'group1',comment => 'group1' , cpu_mth => 'ROUND-ROBIN');
dbms_resource_manager.submit_pending_area();
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group('HR','group1',case 'false' when 'false' then false when 'true' then true else false end);
END;END;
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group(consumer_group => 'group2',comment => 'group2' , cpu_mth => 'ROUND-ROBIN');
dbms_resource_manager.submit_pending_area();
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group('HR','group2',case 'false' when 'false' then false when 'true' then true else false end);
END;END;
DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='PLAN1';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'PLAN1', comment => 'plan1',max_iops => NULL,max_mbps => NULL );
dbms_resource_manager.create_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP1',
comment => '',
switch_elapsed_time => NULL,
max_utilization_limit => 50,
mgmt_p1 => 20, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => NULL ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => NULL ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => '',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'GROUP2',
comment => '',
switch_elapsed_time => NULL,
max_utilization_limit => 8,
mgmt_p1 => 8, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => NULL ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => NULL ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => '',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'OTHER_GROUPS',
comment => '',
switch_elapsed_time => NULL,
max_utilization_limit => 30,
mgmt_p1 => 2, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => NULL ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => NULL ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => '',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.create_plan_directive(
plan => 'PLAN1',
group_or_subplan => 'SYS_GROUP',
comment => '',
switch_elapsed_time => NULL,
max_utilization_limit => 100,
mgmt_p1 => 70, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => NULL ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => NULL ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => '',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => NULL,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
);
dbms_resource_manager.submit_pending_area();
select value into spfileValue from v$parameter where name = 'spfile';
IF spfileValue IS NOT NULL then
EXECUTE IMMEDIATE 'alter system set resource_manager_plan = '||planName||' scope=BOTH';
END IF;
dbms_resource_manager.switch_plan( plan_name => 'PLAN1' , sid => 'orcl' );
END;
selectu.username, u.initial_rsrc_consumer_group
from dba_users uwhere u.username='HR' ;
USERNAMEINITIAL_RSRC_CONSUMER_GROUP
1HRDEFAULT_CONSUMER_GROUP
selects.SID,s.USERNAME , s.RESOURCE_CONSUMER_GROUP
from v_$session s
where s.TERMINAL='pts/52';
SIDUSERNAMERESOURCE_CONSUMER_GROUP
11495HROTHER_GROUPS
BEGIN
dbms_resource_manager.set_initial_consumer_group(
user => 'HR',
consumer_group => 'GROUP1'
);
END;
SQL> conn / as sysdba
Connected.
SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
resource_manager_cpu_allocation integer 4
resource_manager_plan string PLAN1
SQL> connhr/oracle_4U
Connected.
SQL> !ps
PID TTY TIME CMD
26430 pts/52 00:00:00 sqlplus
26662 pts/52 00:00:00 ps
SQL> connhr/oracle_4U
Connected.
SQL>
selects.SID,s.USERNAME , s.RESOURCE_CONSUMER_GROUP
from v_$session s
where s.TERMINAL='pts/52';
SID USERNAME RESOURCE_CONSUMER_GROUP
1 511 HR GROUP1
前台所用的时间：
SQL> conn hr/oracle_4U
Connected.
SQL> selectcount(*)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
作业类与资源组相绑定：
BEGIN
sys.dbms_scheduler.create_job_class(
logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
resource_consumer_group => 'GROUP2',
comments => 'jobclass1',
job_class_name => '"JOBCLASS1"');
END;
grant execute on jobclass1 to hr;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB6"',
job_type => 'PLSQL_BLOCK',
job_action => 'declare
v1 number;
begin
selectcount(*)into v1from t_big a, departments b, departments c;
end;',
start_date => systimestamp at time zone 'PRC',
job_class => '"JOBCLASS1"',
comments => 'job6',
auto_drop => TRUE,
enabled => TRUE);
END;
group1 10秒group21分钟1：6
8%：50% 约 1：6
-------------------------------------
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'"WINDOW1"',
resource_plan=>'PLAN1',
start_date=>to_timestamp_tz('2018-08-04 11:55:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
duration=>numtodsinterval(1, 'minute'),
repeat_interval=>null,
end_date=>null,
window_priority=>'HIGH',
comments=>'WINDOW1');
END;
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
group_name=>'"WINGROUP1"',
window_list=>'"WINDOW1"');
END;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB7"',
program_name => '"HR"."PROGRAM7"',
schedule_name => '"SYS"."WINGROUP1"',
job_class => '"JOBCLASS1"',
comments => 'job7',
auto_drop => TRUE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"HR"."JOB7"', attribute => 'stop_on_window_close', value => TRUE);
sys.dbms_scheduler.enable( '"HR"."JOB7"' );
END;
SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
resource_manager_cpu_allocation integer 4
resource_manager_plan string SCHEDULER:PLAN1
一分钟过后：
SQL> show parameter resource
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
resource_manager_cpu_allocation integer 4
resource_manager_plan string DEFAULT_PLAN
SQL>
作业阵列和持续性轻量级作业：
create table t05317_lw( a timestamp ) ;
create or replace procedure proc05317_lw
is
v_1 number;
begin
select count(*) into v_1 from t_big a, departments b;
insert intot05317_lw values(systimestamp);
commit;
end;
begin
dbms_scheduler.create_job(
job_name => 'hr.job8',
program_name=>'hr.program8',
schedule_name=>'hr.schedule8',
job_style=>'LIGHTWEIGHT');
end;
selectj.job_name,j.JOB_STYLE
from user_scheduler_jobs j ;
JOB_NAMEJOB_STYLE
1JOB2REGULAR
2JOB7REGULAR
3JOB8LIGHTWEIGHT
DECLARE
newjob sys.job;
newjobarr sys.job_array;
BEGIN
-- Create an array of JOB object types
newjobarr := sys.job_array();
-- Allocate sufficient space in the array
newjobarr.extend(100);
-- Add definitions for jobs
FOR i IN 1..100 LOOP
-- Create a JOB object type
newjob := sys.job(job_name => 'HR.JOB8_' || to_char(i),
job_style => 'LIGHTWEIGHT',
job_template => 'HR.PROGRAM8',
enabled => TRUE );
-- Add job to the array
newjobarr(i) := newjob;
END LOOP;
-- Call CREATE_JOBS to create jobs in one transaction
DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
END;
select max(a) - min(a) from t05317_lw;
MAX(A)-MIN(A)
1+000000000 00:00:20.621948
DECLARE
newjob sys.job;
newjobarr sys.job_array;
BEGIN
-- Create an array of JOB object types
newjobarr := sys.job_array();
-- Allocate sufficient space in the array
newjobarr.extend(100);
-- Add definitions for jobs
FOR i IN 1..100 LOOP
-- Create a JOB object type
newjob := sys.job(job_name => 'HR.JOB80_' || to_char(i),
job_style => 'REGULAR',
job_template => 'HR.PROGRAM8',
enabled => TRUE );
-- Add job to the array
newjobarr(i) := newjob;
END LOOP;
-- Call CREATE_JOBS to create jobs in one transaction
DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
END;
select max(a) - min(a) from t05317_lw;
MAX(A)-MIN(A)
1+000000000 00:00:23.043365
页:
[1]