课程第56次（2017-03-02星期四）

botang

1. 补充Moving Data中遗漏的部分：

1. impdp  user1/oracle_4U  parfile=par2.par
   
2. 
   
3. parfile:
   
4. directory=datadir1
   
5. network_link=dblink1
   
6. flashback_scn=9727454
   
7. tables=hr.ta
   
8. remap_schema=hr:user1
   
9. 
   
10. 
    
11. public database link:
    
12. create public database link ....

复制代码

2. 2017-03-02.sql：

1. select u.USERNAME, u.INITIAL_RSRC_CONSUMER_GROUP
   
2.   from dba_users u
   
3.   where u.USERNAME='HR';
   
4.   
   
5.   DECLARE
   
6. spfileValue VARCHAR2(1000);
   
7. execText VARCHAR2(1000);
   
8. scopeValue VARCHAR2(30) := 'MEMORY';
   
9. planName VARCHAR2(100) :='PLAN1';
   
10. BEGIN
    
11. dbms_resource_manager.clear_pending_area();
    
12. dbms_resource_manager.create_pending_area();
    
13. dbms_resource_manager.create_plan( plan => 'PLAN1', comment => 'plan1',max_iops => NULL,max_mbps => NULL );
    
14. dbms_resource_manager.create_plan_directive(
    
15.     plan => 'PLAN1',
    
16.     group_or_subplan => 'GROUP1',
    
17.     comment => '',
    
18.     switch_elapsed_time => NULL,
    
19.      max_utilization_limit => null,
    
20.     mgmt_p1 => NULL, mgmt_p2 => 80, mgmt_p3 => NULL, mgmt_p4 => NULL,
    
21.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
22.     parallel_degree_limit_p1 => NULL ,
    
23.     parallel_target_percentage => NULL ,
    
24.     parallel_queue_timeout => NULL ,
    
25.     parallel_stmt_critical => 'false' ,
    
26.     switch_io_logical => NULL ,
    
27.     switch_io_reqs => NULL ,
    
28.    switch_io_megabytes => NULL ,
    
29.     active_sess_pool_p1 => NULL,
    
30.     queueing_p1 => NULL,
    
31.     switch_group => '',
    
32.     switch_time => NULL,
    
33.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
    
34.     undo_pool => NULL ,
    
35.     max_idle_time => NULL,
    
36.     max_idle_blocker_time => NULL,
    
37.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
38. 
    
39. );
    
40. dbms_resource_manager.create_plan_directive(
    
41.     plan => 'PLAN1',
    
42.     group_or_subplan => 'GROUP2',
    
43.     comment => '',
    
44.     switch_elapsed_time => NULL,
    
45.      max_utilization_limit => null,
    
46.     mgmt_p1 => NULL, mgmt_p2 => 20, mgmt_p3 => NULL, mgmt_p4 => NULL,
    
47.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
48.     parallel_degree_limit_p1 => NULL ,
    
49.     parallel_target_percentage => NULL ,
    
50.     parallel_queue_timeout => NULL ,
    
51.     parallel_stmt_critical => 'false' ,
    
52.     switch_io_logical => NULL ,
    
53.     switch_io_reqs => NULL ,
    
54.    switch_io_megabytes => NULL ,
    
55.     active_sess_pool_p1 => NULL,
    
56.     queueing_p1 => NULL,
    
57.     switch_group => '',
    
58.     switch_time => NULL,
    
59.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
    
60.     undo_pool => NULL ,
    
61.     max_idle_time => NULL,
    
62.     max_idle_blocker_time => NULL,
    
63.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
64. 
    
65. );
    
66. dbms_resource_manager.create_plan_directive(
    
67.     plan => 'PLAN1',
    
68.     group_or_subplan => 'LOW_GROUP',
    
69.     comment => '',
    
70.     switch_elapsed_time => NULL,
    
71.      max_utilization_limit => null,
    
72.     mgmt_p1 => NULL, mgmt_p2 => NULL, mgmt_p3 => 100, mgmt_p4 => NULL,
    
73.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
74.     parallel_degree_limit_p1 => NULL ,
    
75.     parallel_target_percentage => NULL ,
    
76.     parallel_queue_timeout => NULL ,
    
77.     parallel_stmt_critical => 'false' ,
    
78.     switch_io_logical => NULL ,
    
79.     switch_io_reqs => NULL ,
    
80.    switch_io_megabytes => NULL ,
    
81.     active_sess_pool_p1 => NULL,
    
82.     queueing_p1 => NULL,
    
83.     switch_group => '',
    
84.     switch_time => NULL,
    
85.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
    
86.     undo_pool => NULL ,
    
87.     max_idle_time => NULL,
    
88.     max_idle_blocker_time => NULL,
    
89.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
    
90. 
    
91. );
    
92. dbms_resource_manager.create_plan_directive(
    
93.     plan => 'PLAN1',
    
94.     group_or_subplan => 'OTHER_GROUPS',
    
95.     comment => '',
    
96.     switch_elapsed_time => NULL,
    
97.      max_utilization_limit => null,
    
98.     mgmt_p1 => NULL, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => 100,
    
99.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
    
100.     parallel_degree_limit_p1 => NULL ,
     
101.     parallel_target_percentage => NULL ,
     
102.     parallel_queue_timeout => NULL ,
     
103.     parallel_stmt_critical => 'false' ,
     
104.     switch_io_logical => NULL ,
     
105.     switch_io_reqs => NULL ,
     
106.    switch_io_megabytes => NULL ,
     
107.     active_sess_pool_p1 => NULL,
     
108.     queueing_p1 => NULL,
     
109.     switch_group => '',
     
110.     switch_time => NULL,
     
111.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
     
112.     undo_pool => NULL ,
     
113.     max_idle_time => NULL,
     
114.     max_idle_blocker_time => NULL,
     
115.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
116. 
     
117. );
     
118. dbms_resource_manager.create_plan_directive(
     
119.     plan => 'PLAN1',
     
120.     group_or_subplan => 'SYS_GROUP',
     
121.     comment => '',
     
122.     switch_elapsed_time => NULL,
     
123.      max_utilization_limit => null,
     
124.     mgmt_p1 => 100, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
     
125.     mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
     
126.     parallel_degree_limit_p1 => NULL ,
     
127.     parallel_target_percentage => NULL ,
     
128.     parallel_queue_timeout => NULL ,
     
129.     parallel_stmt_critical => 'false' ,
     
130.     switch_io_logical => NULL ,
     
131.     switch_io_reqs => NULL ,
     
132.    switch_io_megabytes => NULL ,
     
133.     active_sess_pool_p1 => NULL,
     
134.     queueing_p1 => NULL,
     
135.     switch_group => '',
     
136.     switch_time => NULL,
     
137.     switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
     
138.     undo_pool => NULL ,
     
139.     max_idle_time => NULL,
     
140.     max_idle_blocker_time => NULL,
     
141.     switch_for_call => case 'true' when 'false' then false when 'true' then true else false end
     
142. 
     
143. );
     
144. dbms_resource_manager.submit_pending_area();
     
145. END;
     
146. 
     
147. 
     
148. select s.USERNAME, s.RESOURCE_CONSUMER_GROUP
     
149. from v$session s
     
150. where s.TERMINAL='pts/3';
     
151. 
     
152. 
     
153. select s.USERNAME, s.RESOURCE_CONSUMER_GROUP
     
154. from v$session s
     
155. where s.TERMINAL='pts/3';
     
156. 
     
157. 
     
158. select  * from dict where table_name like 'DBA_SCHEDULER_%';
     
159. 
     
160. grant execute on jobclass1 to hr;
     
161. 
     
162. ---
     
163. create table hr.t05317_lw( a date ) ;
     
164. 
     
165. create or replace procedure hr.proc05317_lw
     
166. is
     
167. begin
     
168.    insert into  hr.t05317_lw values(sysdate);
     
169.    commit;
     
170. end;
     
171. --em
     
172. select  * from hr.t05317_lw;
     
173. 
     
174. begin
     
175.    dbms_scheduler.create_job(
     
176.    job_name => 'hr.job8',
     
177.    program_name=>'hr.program8',
     
178.    schedule_name=>'hr.schedule8',
     
179.    job_style=>'LIGHTWEIGHT',
     
180.    enabled=> true);
     
181. end;
     
182. 
     
183. DECLARE
     
184. newjob sys.job;
     
185. newjobarr sys.job_array;
     
186. BEGIN
     
187. -- Create an array of JOB object types
     
188.   newjobarr := sys.job_array();
     
189. -- Allocate sufficient space in the array
     
190.   newjobarr.extend(100);
     
191. -- Add definitions for jobs
     
192. FOR i IN 1..100 LOOP
     
193.    -- Create a JOB object type
     
194.    newjob := sys.job(job_name => 'HR.JOB8_' || to_char(i),
     
195.                      job_style => 'LIGHTWEIGHT',
     
196.                      job_template => 'HR.PROGRAM8',
     
197.                    enabled => TRUE );
     
198. -- Add job to the array
     
199.    newjobarr(i) := newjob;
     
200. END LOOP;
     
201. -- Call CREATE_JOBS to create jobs in one transaction
     
202. DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
     
203. END;

复制代码