第59次：2015-06-02星期二

botang

1. select    from dba_scheduler_windows;
   
2. 
   
3. select   username, resource_consumer_group      from  v$session where terminal='pts/6';
   
4. 
   
5. select     username , u.initial_rsrc_consumer_group     from dba_users  u where username in ('SYS','SYSTEM','HR');
   
6. 
   
7. select  * from dba_rsrc_plans;
   
8. 
   
9. BEGIN
   
10. dbms_resource_manager.clear_pending_area();
    
11. dbms_resource_manager.create_pending_area();
    
12. dbms_resource_manager.create_plan( plan => 'PLAN2', comment => 'PLAN2',mgmt_mth => 'RATIO' );
    
13. dbms_resource_manager.create_plan_directive(
    
14.     plan => 'PLAN2',
    
15.     group_or_subplan => 'SYS_GROUP',
    
16.     comment => 'SYS_GROUP',
    
17.     mgmt_p1 => 5   
    
18. );
    
19. 
    
20. dbms_resource_manager.create_plan_directive(
    
21.     plan =>'PLAN2' ,
    
22.     group_or_subplan => 'GROUP1',
    
23.     comment => 'GROUP1',
    
24.     mgmt_p1 => 3
    
25. );
    
26. dbms_resource_manager.create_plan_directive(
    
27.     plan => 'PLAN2',
    
28.     group_or_subplan => 'GROUP2',
    
29.     comment => 'GROUP2',
    
30.     mgmt_p1 => 2
    
31. );
    
32. dbms_resource_manager.create_plan_directive(
    
33.     plan => 'PLAN2',
    
34.     group_or_subplan => 'OTHER_GROUPS',
    
35.     comment => 'OTHER_GROUPS',
    
36.     mgmt_p1 => 1
    
37. );
    
38. dbms_resource_manager.submit_pending_area();
    
39. END;
    
40. 
    
41. 
    
42. select  * from dba_rsrc_plan_directives rpd where rpd.plan='PLAN1';
    
43. 
    
44. 
    
45. select  degree  from dba_indexes where index_name='IBIG' and owner='HR';
    
46. 
    
47. 
    
48. select  username,  status ,resource_consumer_group    from  v$session where  username='HR' ;
    
49. 
    

复制代码

复杂scheduler

1. BEGIN
   
2. /* Complex scheduling example 1: Public Holidays */
   
3. DBMS_SCHEDULER.CREATE_SCHEDULE
   
4. (schedule_name => 'SCHD_2008_PUBLIC_HOLIDAYS'
   
5. ,start_date => to_timestamp_tz('01/01/2008 00:00:00','dd/mm/yyyy hh24:mi:ss')
   
6. ,end_date => to_timestamp_tz('31/12/2008 23:59:59','dd/mm/yyyy hh24:mi:ss')
   
7. ,repeat_interval =>
   
8. 'freq=DAILY;
   
9. bydate=20080101,20080128,20080303,20080321,20080324,20080425,
   
10. 20080929,20081225,20081226'
    
11. ,comments => 'Public Holidays 2008'
    
12. );
    
13. END;
    
14. /
    
15. The following example creates a schedule consisting of all 2008 working days:
    
16. BEGIN
    
17. /* Complex scheduling example 2: Working Days */
    
18. DBMS_SCHEDULER.CREATE_SCHEDULE
    
19. (schedule_name => 'SCHD_2008_WORKING_DAYS'
    
20. ,start_date => to_timestamp_tz('01/01/2008 00:00:00','dd/mm/yyyy hh24:mi:ss')
    
21. ,end_date => to_timestamp_tz('31/12/2008 23:59:59','dd/mm/yyyy hh24:mi:ss')
    
22. ,repeat_interval =>
    
23. 'freq=DAILY;
    
24. byday=MON,TUE,WED,THU,FRI;
    
25. exclude=SCHD_2008_PUBLIC_HOLIDAYS'
    
26. ,comments => 'Run on every working day'
    
27. );
    
28. END;
    
29. /
    
30. The following example creates a schedule consisting of the first working day of every month in 2008:
    
31. BEGIN
    
32. /* Complex scheduling example 3: 1st Working Day in a Month */
    
33. DBMS_SCHEDULER.CREATE_SCHEDULE
    
34. (schedule_name => 'SCHD_2008_1ST_WK_DAY_EACH_MTH'
    
35. ,start_date => to_timestamp_tz('01/01/2008 00:00:00','dd/mm/yyyy hh24:mi:ss')
    
36. ,end_date => to_timestamp_tz('31/12/2008 23:59:59','dd/mm/yyyy hh24:mi:ss')
    
37. ,repeat_interval =>
    
38. 'freq=MONTHLY;
    
39. byday=MON,TUE,WED,THU,FRI;
    
40. exclude=SCHD_2008_PUBLIC_HOLIDAYS;
    
41. bysetpos=1'
    
42. ,comments => 'Run on 1st working day every month'
    
43. );
    
44. END;
    
45. /
    
46. -------------------------------------------------------------
    
47. BEGIN
    
48. /* Complex scheduling example 4: 1st and 3rd Monday each month */
    
49. DBMS_SCHEDULER.CREATE_SCHEDULE
    
50. (schedule_name => 'SCHD_1ST_3RD_MON_EACH_MTH'
    
51. ,start_date => to_timestamp_tz('03/12/2007 05:00:00','dd/mm/yyyy hh24:mi:ss')
    
52. ,repeat_interval =>
    
53. 'freq=MONTHLY;
    
54. byday=1MON,3MON'
    
55. ,comments => 'Run on every 1st and 3rd Monday each month'
    
56. );
    
57. END;
    
58. /
    
59. The following example creates a schedule consisting of the last day of each quarter:
    
60. BEGIN
    
61. /* Complex scheduling example 5: Quarter */
    
62. DBMS_SCHEDULER.CREATE_SCHEDULE
    
63. (schedule_name => 'SCHD_LAST_DAY_EACH_QTR'
    
64. ,start_date => to_timestamp_tz('31/12/2008 05:00:00','dd/mm/yyyy hh24:mi:ss')
    
65. ,repeat_interval =>
    
66. 'freq=YEARLY;
    
67. bymonth=MAR,JUN,SEP,DEC;
    
68. bymonthday=-1'
    
69. ,comments => 'Run on every last day each quarter'
    
70. );
    
71. END;
    
72. /
    
73. -------------------------------------------------------------
    
74. CREATE OR REPLACE PROCEDURE show_schedule
    
75. (schedule_name in varchar2
    
76. ,start_date in timestamp
    
77. ,repeat_interval in varchar2
    
78. ,iteration in number)
    
79. AS
    
80. v_start_date timestamp;
    
81. v_after_date timestamp;
    
82. v_next_date timestamp;
    
83. BEGIN
    
84. dbms_output.put_line (schedule_name);
    
85. v_start_date := start_date;
    
86. v_after_date := v_start_date-(1/86400);
    
87. for i in 1..iteration loop
    
88. dbms_scheduler.evaluate_calendar_string
    
89. (repeat_interval
    
90. ,v_start_date
    
91. ,v_after_date
    
92. ,v_next_date
    
93. );
    
94. dbms_output.put_line (v_next_date);
    
95. v_after_date:= v_next_date;
    
96. end loop;
    
97. dbms_output.put_line ('============================');
    
98. END;
    
99. /
    
100. -- --------------------------------------------------------
     
101. set serveroutput on
     
102. exec dbms_output.enable (1000000);
     
103. begin
     
104. show_schedule
     
105. (schedule_name => 'SCHD_2008_PUBLIC_HOLIDAYS'
     
106. ,start_date => to_timestamp_tz('01/01/2008 00:00:00','dd/mm/yyyy hh24:mi:ss')
     
107. ,repeat_interval =>
     
108. 'freq=DAILY;
     
109. bydate=20080101,20080128,20080303,20080321,20080324,20080425,
     
110. 20080929,20081225,20081226'
     
111. ,iteration => 9);
     
112. show_schedule
     
113. (schedule_name => 'SCHD_2008_WORKING_DAYS'
     
114. ,start_date => to_timestamp_tz('01/01/2008 00:00:00','dd/mm/yyyy hh24:mi:ss')
     
115. ,repeat_interval =>
     
116. 'freq=DAILY;
     
117. byday=MON,TUE,WED,THU,FRI;
     
118. exclude=SCHD_2008_PUBLIC_HOLIDAYS'
     
119. ,iteration => 21);
     
120. show_schedule
     
121. (schedule_name => 'SCHD_2008_1ST_WK_DAY_EACH_MTH'
     
122. ,start_date => to_timestamp_tz('01/01/2008 00:00:00','dd/mm/yyyy hh24:mi:ss')
     
123. ,repeat_interval =>
     
124. 'freq=MONTHLY;
     
125. byday=MON,TUE,WED,THU,FRI;
     
126. exclude=SCHD_2008_PUBLIC_HOLIDAYS;
     
127. bysetpos=1'
     
128. ,iteration => 12);
     
129. show_schedule
     
130. (schedule_name => 'SCHD_1ST_3RD_MON_EACH_MTH'
     
131. ,start_date => to_timestamp_tz('03/12/2007 05:00:00','dd/mm/yyyy hh24:mi:ss')
     
132. ,repeat_interval =>
     
133. 'freq=MONTHLY;
     
134. byday=1MON,3MON'
     
135. ,iteration => 26);
     
136. show_schedule
     
137. (schedule_name => 'SCHD_LAST_DAY_EACH_QTR'
     
138. ,start_date => to_timestamp_tz('31/12/2008 05:00:00','dd/mm/yyyy hh24:mi:ss')
     
139. ,repeat_interval =>
     
140. 'freq=YEARLY;
     
141. bymonth=MAR,JUN,SEP,DEC;
     
142. bymonthday=-1'
     
143. ,iteration => 5);
     
144. end;
     
145. /
     
146. set serverout off
     
147. -- ---------------------

复制代码

event_based_jobs.sql：

1. CONN sys/oracle AS SYSDBA
   
2. DROP USER test CASCADE;
   
3. 
   
4. CREATE USER test IDENTIFIED BY test QUOTA UNLIMITED ON USERS;
   
5. GRANT CONNECT TO test;
   
6. GRANT CREATE TABLE TO test;
   
7. GRANT CREATE SEQUENCE TO test;
   
8. GRANT CREATE TYPE TO test;
   
9. GRANT AQ_ADMINISTRATOR_ROLE TO test;
   
10. GRANT CREATE JOB TO test;
    
11. 
    
12. 
    
13. CONN test/test
    
14. DROP TABLE scheduler_test;
    
15. DROP SEQUENCE scheduler_test_seq;
    
16. 
    
17. CREATE TABLE scheduler_test (
    
18.   id            NUMBER(10)    NOT NULL,
    
19.   created_date  DATE          NOT NULL,
    
20.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
    
21. );
    
22. 
    
23. CREATE SEQUENCE scheduler_test_seq;
    
24. 
    
25. 
    
26. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
    
27.   event_name  VARCHAR2(30)
    
28. );
    
29. /
    
30. 
    
31. BEGIN
    
32.   -- Create a queue table to hold the event queue.
    
33.   DBMS_AQADM.create_queue_table(
    
34.     queue_table        => 'event_queue_tab',
    
35.     queue_payload_type => 't_event_queue_payload',
    
36.     multiple_consumers => TRUE,
    
37.     comment            => 'Queue Table For Event Messages');
    
38. 
    
39.   -- Create the event queue.
    
40.   DBMS_AQADM.create_queue (
    
41.     queue_name  => 'event_queue',
    
42.     queue_table => 'event_queue_tab');
    
43. 
    
44.   -- Start the event queue.
    
45.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
    
46. END;
    
47. /
    
48. 
    
49. BEGIN
    
50.    DBMS_SCHEDULER.create_job (
    
51.       job_name        => 'event_based_job',
    
52.       job_type        => 'PLSQL_BLOCK',
    
53.       job_action      => 'BEGIN
    
54.                             INSERT INTO scheduler_test (id, created_date)
    
55.                             VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);
    
56.                             COMMIT;
    
57.                           END;',
    
58.       start_date      => SYSTIMESTAMP,
    
59.       event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
    
60.       queue_spec      => 'event_queue',
    
61.       enabled         => TRUE);
    
62. END;
    
63. /
    
64. 
    
65. SELECT * FROM scheduler_test;
    
66. 
    
67. DECLARE
    
68.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
    
69.   l_message_properties  DBMS_AQ.message_properties_t;
    
70.   l_message_handle      RAW(16);
    
71.   l_queue_msg           t_event_queue_payload;
    
72. BEGIN
    
73.   l_queue_msg := t_event_queue_payload('give_me_a_prod');
    
74. 
    
75.   DBMS_AQ.enqueue(queue_name          => 'event_queue',
    
76.                   enqueue_options     => l_enqueue_options,
    
77.                   message_properties  => l_message_properties,
    
78.                   payload             => l_queue_msg,
    
79.                   msgid               => l_message_handle);
    
80.   COMMIT;
    
81. END;
    
82. /
    
83. 
    
84. /*
    
85. SELECT * FROM scheduler_test;
    
86. 
    
87. -- Remove the job.
    
88. EXEC DBMS_SCHEDULER.drop_job('event_based_job');
    
89.   
    
90. -- Stop the event queue.
    
91. EXEC DBMS_AQADM.stop_queue (queue_name => 'event_queue');
    
92. 
    
93. -- Drop the event queue.
    
94. EXEC DBMS_AQADM.drop_queue (queue_name => 'event_queue');
    
95. 
    
96. -- Remove the queue table.
    
97. EXEC DBMS_AQADM.drop_queue_table(queue_table => 'event_queue_tab');
    
98. 
    
99. DROP TYPE t_event_queue_payload;
    
100. DROP TABLE scheduler_test;
     
101. DROP SEQUENCE scheduler_test_seq;
     
102. PURGE RECYCLEBIN;
     
103. */
     

复制代码

04314chain.sql

1. CREATE TABLE scheduler_test (
   
2. 
   
3.   id            NUMBER(10)    NOT NULL,
   
4. 
   
5.   description   VARCHAR2(20)  NOT NULL,
   
6. 
   
7.   created_date  DATE          NOT NULL,
   
8. 
   
9.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
   
10. 
    
11. );
    
12. 
    
13. 
    
14. CREATE SEQUENCE scheduler_test_seq;
    
15. 
    
16. BEGIN
    
17. 
    
18.   DBMS_SCHEDULER.create_program (
    
19. 
    
20.     program_name   => 'test_program_1',
    
21. 
    
22.     program_type   => 'PLSQL_BLOCK',
    
23. 
    
24.     program_action => 'BEGIN
    
25. 
    
26.                          INSERT INTO scheduler_test (id, description, created_date)
    
27. 
    
28.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
    
29. 
    
30.                          COMMIT;
    
31. 
    
32.                        END;',
    
33. 
    
34.     enabled        => TRUE,
    
35. 
    
36.     comments       => 'Program for first link in the chain.');
    
37. 
    
38. 
    
39. 
    
40.   DBMS_SCHEDULER.create_program (
    
41. 
    
42.     program_name   => 'test_program_2',
    
43. 
    
44.     program_type   => 'PLSQL_BLOCK',
    
45. 
    
46.     program_action => 'BEGIN
    
47. 
    
48.                          INSERT INTO scheduler_test (id, description, created_date)
    
49. 
    
50.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
    
51. 
    
52.                          COMMIT;
    
53. 
    
54.                        END;',
    
55. 
    
56.     enabled        => TRUE,
    
57. 
    
58.     comments       => 'Program for second link in the chain.');
    
59. 
    
60. 
    
61. 
    
62.   DBMS_SCHEDULER.create_program (
    
63. 
    
64.     program_name   => 'test_program_3',
    
65. 
    
66.     program_type   => 'PLSQL_BLOCK',
    
67. 
    
68.     program_action => 'BEGIN
    
69. 
    
70.                          INSERT INTO scheduler_test (id, description, created_date)
    
71. 
    
72.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
    
73. 
    
74.                          COMMIT;
    
75. 
    
76.                        END;',
    
77. 
    
78.     enabled        => TRUE,
    
79. 
    
80.     comments       => 'Program for last link in the chain.');
    
81. 
    
82. END;
    
83. 
    
84. 
    
85. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
    
86. 
    
87.   event_name  VARCHAR2(30)
    
88. 
    
89. );
    
90. 
    
91. 
    
92. 
    
93. BEGIN
    
94. 
    
95.   -- Create a queue table to hold the event queue.
    
96. 
    
97.   DBMS_AQADM.create_queue_table(
    
98. 
    
99.     queue_table        => 'event_queue_tab',
    
100. 
     
101.     queue_payload_type => 't_event_queue_payload',
     
102. 
     
103.     multiple_consumers => TRUE,
     
104. 
     
105.     comment            => 'Queue Table For Event Messages');
     
106. 
     
107. 
     
108. 
     
109.   -- Create the event queue.
     
110. 
     
111.   DBMS_AQADM.create_queue (
     
112. 
     
113.     queue_name  => 'event_queue',
     
114. 
     
115.     queue_table => 'event_queue_tab');
     
116. 
     
117. 
     
118. 
     
119.   -- Start the event queue.
     
120. 
     
121.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
     
122. 
     
123. END;
     
124. 
     
125. 
     
126. 
     
127. BEGIN
     
128. sys.dbms_scheduler.create_event_schedule(
     
129. event_condition => 'tab.user_data.event_name = ''give_me_a_orange''',
     
130. queue_spec => 'TEST.EVENT_QUEUE',
     
131. start_date => systimestamp at time zone '+8:00',
     
132. comments => 'schedule_event',
     
133. schedule_name => '"TEST"."SCHEDULE_EVENT"');
     
134. END;
     
135. 
     
136. 
     
137. BEGIN
     
138. 
     
139.   DBMS_SCHEDULER.create_chain (
     
140. 
     
141.     chain_name          => 'test_chain_1',
     
142. 
     
143.     rule_set_name       => NULL,
     
144. 
     
145.     evaluation_interval => NULL,
     
146. 
     
147.     comments            => 'A test chain.');
     
148. 
     
149. END;
     
150. 
     
151. 
     
152. 
     
153. 
     
154. SELECT
     
155. 
     
156.        chain_name,
     
157. 
     
158.        rule_set_owner,
     
159. 
     
160.        rule_set_name,
     
161. 
     
162.        number_of_rules,
     
163. 
     
164.        number_of_steps,
     
165. 
     
166.        enabled,
     
167. 
     
168.        comments
     
169. 
     
170. FROM   user_scheduler_chains;
     
171. 
     
172. 
     
173. 
     
174. 
     
175. 
     
176. 
     
177. 
     
178. BEGIN
     
179.   
     
180.   DBMS_SCHEDULER.define_chain_event_step (
     
181. 
     
182.     chain_name   => 'test_chain_1',
     
183. 
     
184.     step_name    => 'chain_step_1',
     
185. 
     
186.     event_schedule_name => 'TEST.SCHEDULE_EVENT');
     
187. 
     
188. 
     
189. 
     
190.   DBMS_SCHEDULER.define_chain_step (
     
191. 
     
192.     chain_name   => 'test_chain_1',
     
193. 
     
194.     step_name    => 'chain_step_2',
     
195. 
     
196.     program_name => 'test_program_1');
     
197. 
     
198. 
     
199. 
     
200.   DBMS_SCHEDULER.define_chain_step (
     
201. 
     
202.     chain_name   => 'test_chain_1',
     
203. 
     
204.     step_name    => 'chain_step_3',
     
205. 
     
206.     program_name => 'test_program_2');
     
207. 
     
208. 
     
209. 
     
210.   DBMS_SCHEDULER.define_chain_step (
     
211. 
     
212.     chain_name   => 'test_chain_1',
     
213. 
     
214.     step_name    => 'chain_step_4',
     
215. 
     
216.     program_name => 'test_program_3');
     
217. 
     
218. END;
     
219. 
     
220. 
     
221. 
     
222. 
     
223. 
     
224. 
     
225. BEGIN
     
226.   
     
227.   DBMS_SCHEDULER.define_chain_rule (
     
228.   
     
229. 
     
230.     chain_name => 'test_chain_1',
     
231. 
     
232.     condition  => '1=1',
     
233. 
     
234.     action     => 'START chain_step_1',
     
235. 
     
236.     rule_name  => 'chain_rule_1',
     
237. 
     
238.     comments   => 'First link in the chain.');
     
239. 
     
240. 
     
241. 
     
242. 
     
243. 
     
244. 
     
245. 
     
246.   DBMS_SCHEDULER.define_chain_rule (
     
247.   
     
248. 
     
249.     chain_name => 'test_chain_1',
     
250. 
     
251.     condition  => 'chain_step_1 completed',
     
252. 
     
253.     action     => 'START chain_step_2',
     
254. 
     
255.     rule_name  => 'chain_rule_2',
     
256. 
     
257.     comments   => 'Second  link in the chain.');
     
258. 
     
259. 
     
260. 
     
261.   DBMS_SCHEDULER.define_chain_rule (
     
262. 
     
263.     chain_name => 'test_chain_1',
     
264. 
     
265.     condition  => 'chain_step_2 completed',
     
266. 
     
267.     action     => 'START chain_step_3',
     
268. 
     
269.     rule_name  => 'chain_rule_3',
     
270. 
     
271.     comments   => 'Third  link in the chain.');
     
272. 
     
273. 
     
274. 
     
275.   DBMS_SCHEDULER.define_chain_rule (
     
276. 
     
277.     chain_name => 'test_chain_1',
     
278. 
     
279.     condition  => 'chain_step_3 completed',
     
280. 
     
281.     action     => 'START chain_step_4',
     
282. 
     
283.     rule_name  => 'chain_rule_4',
     
284. 
     
285.     comments   => 'Fourth link in the chain.');
     
286. 
     
287. 
     
288. 
     
289.   DBMS_SCHEDULER.define_chain_rule (
     
290. 
     
291.     chain_name => 'test_chain_1',
     
292. 
     
293.     condition  => 'chain_step_4 completed',
     
294. 
     
295.     action     => 'END',
     
296. 
     
297.     rule_name  => 'chain_rule_5',
     
298. 
     
299.     comments   => 'End of the chain.');
     
300. 
     
301. END;
     
302. 
     
303. 
     
304. 
     
305. BEGIN
     
306.   DBMS_SCHEDULER.ENABLE ('test_chain_1');
     
307. END;
     
308. 
     
309. 
     
310. 
     
311. 
     
312. 
     
313. SELECT *
     
314. 
     
315. FROM   user_scheduler_running_chains;
     
316. 
     
317. 
     
318. 
     
319. 
     
320. 
     
321. 
     
322. select  * from  scheduler_test;
     
323. 
     
324. 
     
325. 
     
326. 
     
327. 
     
328. DECLARE
     
329. 
     
330.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
     
331. 
     
332.   l_message_properties  DBMS_AQ.message_properties_t;
     
333. 
     
334.   l_message_handle      RAW(16);
     
335. 
     
336.   l_queue_msg           t_event_queue_payload;
     
337. 
     
338. BEGIN
     
339. 
     
340.   l_queue_msg := t_event_queue_payload('give_me_a_orange');
     
341. 
     
342. 
     
343. 
     
344.   DBMS_AQ.enqueue(queue_name          => 'event_queue',
     
345. 
     
346.                   enqueue_options     => l_enqueue_options,
     
347. 
     
348.                   message_properties  => l_message_properties,
     
349. 
     
350.                   payload             => l_queue_msg,
     
351. 
     
352.                   msgid               => l_message_handle);
     
353. 
     
354.   COMMIT;
     
355. 
     
356. END;
     

复制代码