课程第8次

secoug

Notice: This blog is written by Bo Tang.

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 => ?,comment => ? , cpu_mth => ?);
   
5. dbms_resource_manager.submit_pending_area();
   
6. END;

复制代码

1. BEGIN
   
2. dbms_resource_manager_privs.grant_switch_consumer_group(?,?,case ? when 'false' then false when 'true' then true else false end);
   
3. END;

复制代码

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

复制代码

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

复制代码

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

复制代码

1. select * from dba_users u where u.username in ('HR','SYS','SYSTEM');
   
2. 
   
3. grant all  on window1 to hr;
   
4. 
   
5. grant execute on jobclass1 to hr;
   
6. 
   
7. select  s."USERNAME", s.sid, s."SERIAL#", s."TERMINAL", s."RESOURCE_CONSUMER_GROUP"
   
8. from v$session s
   
9. where s."USERNAME"='HR';

复制代码

----job8

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 employees a, employees b;
   
8.    insert into  t05317_lw values(systimestamp);
   
9.    commit;
   
10. end;
    
11. 
    
12. 
    
13. 单个轻量级作业：
    
14. begin
    
15.    dbms_scheduler.create_job(
    
16.    job_name => 'hr.job8',
    
17.    program_name=>'hr.program8',
    
18.    schedule_name=>'hr.schedule8',
    
19.    job_style=>'LIGHTWEIGHT');
    
20. end;
    
21. 
    
22. begin
    
23.    dbms_scheduler.enable('HR.JOB8');
    
24. end;
    
25. 
    
26. select  * from hr.t05317_lw;
    
27. 
    
28. truncate table hr.t05317_lw;
    

复制代码

1. truncate table  t05317_lw;
   
2. 
   
3. select  * from t05317_lw;
   
4. 
   
5. DECLARE
   
6. newjob sys.job;
   
7. newjobarr sys.job_array;
   
8. BEGIN
   
9. -- Create an array of JOB object types
   
10.   newjobarr := sys.job_array();
    
11. -- Allocate sufficient space in the array
    
12.   newjobarr.extend(100);
    
13. -- Add definitions for jobs
    
14. FOR i IN 1..100
    
15.    LOOP
    
16.    -- Create a JOB object type
    
17.    newjob := sys.job(job_name => 'HR.JOB8_' || to_char(i),
    
18.                      job_style => 'LIGHTWEIGHT',
    
19.                      job_template => 'HR.PROGRAM8',
    
20.                    enabled => TRUE );
    
21. -- Add job to the array
    
22.    newjobarr(i) := newjob;
    
23. END LOOP;
    
24. -- Call CREATE_JOBS to create jobs in one transaction
    
25. DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
    
26. END;
    
27. 
    
28. 
    
29. select count(*) from t05317_lw;
    
30. 
    
31. select max(a) - min(a) from t05317_lw;
    
32. 
    
33. ---+000000000 00:00:04.011926
    
34. 
    
35. DECLARE
    
36. newjob sys.job;
    
37. newjobarr sys.job_array;
    
38. BEGIN
    
39. -- Create an array of JOB object types
    
40.   newjobarr := sys.job_array();
    
41. -- Allocate sufficient space in the array
    
42.   newjobarr.extend(100);
    
43. -- Add definitions for jobs
    
44. FOR i IN 1..100
    
45.    LOOP
    
46.    -- Create a JOB object type
    
47.    newjob := sys.job(job_name => 'HR.JOB8_' || to_char(i),
    
48.                      job_style => 'REGULAR',
    
49.                      job_template => 'HR.PROGRAM8',
    
50.                    enabled => TRUE );
    
51. -- Add job to the array
    
52.    newjobarr(i) := newjob;
    
53. END LOOP;
    
54. -- Call CREATE_JOBS to create jobs in one transaction
    
55. commit;
    
56. END;
    
57. 
    
58. 
    
59. select count(*) from t05317_lw;
    
60. 
    
61. select max(a) - min(a) from t05317_lw;

复制代码