|
Notice: This blog is written by Bo Tang.
- BEGIN
- dbms_resource_manager.clear_pending_area();
- dbms_resource_manager.create_pending_area();
- dbms_resource_manager.create_consumer_group(consumer_group => ?,comment => ? , cpu_mth => ?);
- dbms_resource_manager.submit_pending_area();
- END;
复制代码- BEGIN
- dbms_resource_manager_privs.grant_switch_consumer_group(?,?,case ? when 'false' then false when 'true' then true else false end);
- END;
复制代码- BEGIN
- dbms_resource_manager.set_initial_consumer_group(
- user => 'HR',
- consumer_group => 'GROUP1'
- );
- END;
复制代码- BEGIN
- sys.dbms_scheduler.create_job_class(
- logging_level => DBMS_SCHEDULER.LOGGING_RUNS,
- resource_consumer_group => 'GROUP2',
- job_class_name => '"JOBCLASS1"');
- END;
复制代码- BEGIN
- DBMS_SCHEDULER.CREATE_WINDOW(
- window_name=>'"WINDOW1"',
- resource_plan=>'PLAN1',
- start_date=>to_timestamp_tz('2019-09-16 22:00:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
- duration=>numtodsinterval(3, 'minute'),
- repeat_interval=>'FREQ=HOURLY',
- end_date=>null,
- window_priority=>'HIGH',
- comments=>'');
- END;
复制代码- select * from dba_users u where u.username in ('HR','SYS','SYSTEM');
- grant all on window1 to hr;
- grant execute on jobclass1 to hr;
- select s."USERNAME", s.sid, s."SERIAL#", s."TERMINAL", s."RESOURCE_CONSUMER_GROUP"
- from v$session s
- where s."USERNAME"='HR';
复制代码 ----job8
- create table t05317_lw( a timestamp ) ;
- create or replace procedure proc05317_lw
- is
- v_1 number;
- begin
- select count(*) into v_1 from employees a, employees b;
- insert into t05317_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;
-
- begin
- dbms_scheduler.enable('HR.JOB8');
- end;
- select * from hr.t05317_lw;
- truncate table hr.t05317_lw;
复制代码- truncate table t05317_lw;
- select * from t05317_lw;
- 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 count(*) from t05317_lw;
-
- select max(a) - min(a) from t05317_lw;
-
- ---+000000000 00:00:04.011926
-
- 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 => '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
- commit;
- END;
- select count(*) from t05317_lw;
-
- select max(a) - min(a) from t05317_lw;
复制代码
|
|