Bo's Oracle Station

查看: 1794|回复: 0

课程第53次:2016-07-20星期三

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2016-7-20 23:28:35 | 显示全部楼层 |阅读模式
本帖最后由 botang 于 2016-7-21 09:16 编辑

课程第53次:2016-07-20星期三
【继续1Z0-053的第17章】Scheduler

Screenshot.png



Screenshot.png

参考别期的帖子:
https://www.botangdb.com/forum.php?mod=viewthread&tid=266&extra=page%3D1

昨天晚上的随堂SQL:

job5.sql:
  1. select  * from dba_scheduler_global_attribute;

  2. begin
  3. DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
  4. ('email_server','station90.example.com');
  5. end;

  6. select  * from dba_scheduler_global_attribute;

  7. begin
  8.   DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
  9.   ('email_sender','oracle@example.com');
  10. end;

  11. select  * from dba_scheduler_global_attribute;


  12. begin
  13.   DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  14.   job_name       =>'HR.JOB5',
  15.     events   =>'job_succeeded,job_completed',
  16.   recipients     =>'oracle@example.com');
  17. end;

  18. ---
  19. select  * from dba_scheduler_notifications;
  20. ---

  21. begin
  22. DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
  23.   job_name         =>'HR.JOB5');
  24. end;

  25. ----
  26. select  * from dba_scheduler_notifications;

  27. ---

  28. begin
  29. DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  30.   job_name       =>'HR.JOB5',
  31.     events   => 'job_failed',
  32.   recipients     =>'oracle@example.com');
  33. end;


  34. select  * from dba_scheduler_notifications;
  35. ----






复制代码

job6.sql:

  1. --hr:
  2. DROP TABLE scheduler_test;
  3. DROP SEQUENCE scheduler_test_seq;

  4. CREATE TABLE scheduler_test (
  5.   id            NUMBER(10)    NOT NULL,
  6.   description   VARCHAR2(20)  NOT NULL,
  7.   created_date  DATE          NOT NULL,
  8.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
  9. );

  10. CREATE SEQUENCE scheduler_test_seq;

  11. BEGIN
  12.   DBMS_SCHEDULER.create_program (
  13.     program_name   => 'test_program_1',
  14.     program_type   => 'PLSQL_BLOCK',
  15.     program_action => 'BEGIN
  16.                          INSERT INTO scheduler_test (id, description, created_date)
  17.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
  18.                          COMMIT;
  19.                        END;',
  20.     enabled        => TRUE,
  21.     comments       => 'Step2');

  22.   DBMS_SCHEDULER.create_program (
  23.     program_name   => 'test_program_2',
  24.     program_type   => 'PLSQL_BLOCK',
  25.     program_action => 'BEGIN
  26.                          INSERT INTO scheduler_test (id, description, created_date)
  27.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
  28.                          COMMIT;
  29.                        END;',
  30.     enabled        => TRUE,
  31.     comments       => 'Step3');

  32.   DBMS_SCHEDULER.create_program (
  33.     program_name   => 'test_program_3',
  34.     program_type   => 'PLSQL_BLOCK',
  35.     program_action => 'BEGIN
  36.                          INSERT INTO scheduler_test (id, description, created_date)
  37.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
  38.                          COMMIT;
  39.                        END;',
  40.     enabled        => TRUE,
  41.     comments       => 'Step4');
  42. END;

  43. --SYS
  44. em做

  45. 如果HR本人,需要以下权限
  46. BEGIN
  47.   DBMS_RULE_ADM.grant_system_privilege(
  48.      privilege    => DBMS_RULE_ADM.create_rule_set_obj,
  49.      grantee      => 'HR',
  50.      grant_option => FALSE);

  51.   DBMS_RULE_ADM.grant_system_privilege(
  52.      privilege    => DBMS_RULE_ADM.create_evaluation_context_obj,
  53.      grantee      => 'HR',
  54.      grant_option => FALSE);

  55.   DBMS_RULE_ADM.grant_system_privilege(
  56.      privilege    => DBMS_RULE_ADM.create_rule_obj,
  57.      grantee      => 'HR',
  58.      grant_option => FALSE);
  59. END;

  60. 另外:
  61. analyze_chain:
  62. dbms_scheduler.analyze_chain(chain_name => ,rules => ,steps => ,step_pairs => );
  63. evaluate_running_chain:
  64. dbms_scheduler.evaluate_running_chain(job_name => );

  65. 规则制定:
  66. 每一条规则都要有“condition”和“action”。

  67. 如果condition为TRUE, the action执行。 Conditions are usually based on the outcome of one or more previous steps.
  68. A condition accepts Boolean and numeric integer values in an expression. The entire expression must evaluate to a Boolean value.

  69. The simplified syntax of a chain condition is as follows:
  70. 'factor|NOT(factor)[AND|OR factor]'

  71. factor:
  72. stepname ERROR_CODE number|[NOT]step_condition

  73. When creating a rule condition using the simplified syntax:
  74. You specify one or more factors, and a Boolean operator (AND, OR, or NOT).
  75. A factor can be either a simple Boolean value (TRUE or FALSE) or a chain condition. A chain condition describes the condition of another step in the job chain. You can use the following to describe the chain condition:
  76. The current state of the chain step:
  77. SUCCEEDED
  78. FAILED
  79. STOPPED
  80. COMPLETED
  81. The error code returned by the chain step. The error is a numeric value, and can be:
  82. Evaluated within a numeric clause
  83. Compared to a list of values using an IN clause
  84. You can use negative factors, by enclosing the factor in parentheses and prefixing the factor with the NOT operator.

  85. Examples:

  86. 'step1 SUCCEEDED AND step2 ERROR_CODE = 3'

  87. 'TRUE'

  88. 'step3 NOT COMPLETED AND NOT (step1 SUCCEEDED)'

  89. 'step2 ERROR_CODE NOT IN (1,2,3)'

  90. You can also refer to attributes of chain steps of the chain (this is called bind-variable syntax). The syntax is as follows:

  91. STEP_NAME.ATTRIBUTE

  92. Possible attributes are: completed, state, start_date, end_date, error_code, and duration.
  93. Possible values for the state attribute include: 'NOT_STARTED', 'SCHEDULED', 'RUNNING', 'PAUSED', 'SUCCEEDED', 'FAILED', and 'STOPPED'.
  94. If a step is in the state 'SUCCEEDED', 'FAILED', or 'STOPPED', its completed attribute is set to 'TRUE'; otherwise, completed is 'FALSE'.
  95. Some examples of the bind variable syntax are:

  96. ':step1.state=''SUCCEEDED'' and :step2.error_code=3'

  97. '1=1'

  98. ':step3.state != ''COMPLETED'''

  99. ':step2.error_code not in (1,2,3)'

  100. ':step1.state = ''NOT_STARTED'''

  101. The rule action specifies what is to be done as a result of the rule being triggered. A typical action is to run a specified step. Possible actions include:

  102. START step_1[,step_2...]
  103. STOP step_1[,step_2...]
  104. END [{end_value | step_name.error_code}]
  105. When the job starts and at the end of each step, all rules are evaluated to see what action or actions occur next. You can also configure rules to be evaluated at regular intervals by using the EVALUATION_INTERVAL attribute of the chain.

  106. You add a rule to a chain with the DEFINE_CHAIN_RULE procedure:

  107. BEGIN
  108. DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
  109. CHAIN_NAME => 'bulk_load_chain',
  110. CONDITION => 'TRUE', -- starting step
  111. ACTION => 'START load_data_evt,stop_when_disk_full_evt',
  112. Rule_Name => 'dataload_rule1',
  113. COMMENTS => 'start the chain');

  114. DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
  115. CHAIN_NAME => 'bulk_load_chain',
  116. CONDITION => 'load_data_evt COMPLETED',
  117. ACTION => 'START do_bulk_load',
  118. RULE_NAME => 'dataload_rule2');
  119. END;
  120. 又比如:
  121. BEGIN
  122.   DBMS_SCHEDULER.define_chain_rule (
  123.     chain_name => 'test_chain_1',
  124.     condition  => 'TRUE',
  125.     action     => 'START chain_step_1',
  126.     rule_name  => 'chain_rule_1',
  127.     comments   => 'First link in the chain.');

  128.   DBMS_SCHEDULER.define_chain_rule (
  129.     chain_name => 'test_chain_1',
  130.     condition  => 'chain_step_1 completed',
  131.     action     => 'START chain_step_2',
  132.     rule_name  => 'chain_rule_2',
  133.     comments   => 'Second link in the chain.');

  134.   DBMS_SCHEDULER.define_chain_rule (
  135.     chain_name => 'test_chain_1',
  136.     condition  => 'chain_step_2 completed',
  137.     action     => 'START chain_step_3',
  138.     rule_name  => 'chain_rule_3',
  139.     comments   => 'Third link in the chain.');

  140.   DBMS_SCHEDULER.define_chain_rule (
  141.     chain_name => 'test_chain_1',
  142.     condition  => 'chain_step_3 completed',
  143.     action     => 'END',
  144.     rule_name  => 'chain_rule_4',
  145.     comments   => 'End of the chain.');
  146. END;
  147. /

  148. SET LINESIZE 200
  149. COLUMN owner FORMAT A10
  150. COLUMN chain_name FORMAT A15
  151. COLUMN rule_owner FORMAT A10
  152. COLUMN rule_name FORMAT A15
  153. COLUMN condition FORMAT A25
  154. COLUMN action FORMAT A20
  155. COLUMN comments FORMAT A25

  156. SELECT owner,
  157.        chain_name,
  158.        rule_owner,
  159.        rule_name,
  160.        condition,
  161.        action,
  162.        comments
  163. FROM   dba_scheduler_chain_rules
  164. ORDER BY owner, chain_name, rule_owner, rule_name;
  165. /

  166. SET LINESIZE 200
  167. COLUMN owner FORMAT A10
  168. COLUMN job_name FORMAT A20
  169. COLUMN chain_owner FORMAT A10
  170. COLUMN chain_name FORMAT A15
  171. COLUMN step_name FORMAT A25

  172. SELECT owner,
  173.        job_name,
  174.        chain_owner,
  175.        chain_name,
  176.        step_name,
  177.        state
  178. FROM   dba_scheduler_running_chains
  179. ORDER BY owner, job_name, chain_name, step_name;

  180. BEGIN
  181.   DBMS_SCHEDULER.run_chain (
  182.     chain_name    =>  'test_chain_1',
  183.     job_name      =>  'test_chain_1_run_job',
  184.     start_steps   =>  'chain_step_1, chain_step_2, chain_step_3');
  185. END;
  186. /
  187. /*
  188. EXEC DBMS_SCHEDULER.drop_job(job_name => 'test_chain_1_job');

  189. EXEC DBMS_SCHEDULER.drop_chain (chain_name  => 'test_chain_1');

  190. EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_program_1');
  191. EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_program_2');
  192. EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_program_3');

  193. DROP TABLE scheduler_test;
  194. DROP SEQUENCE scheduler_test_seq;
  195. PURGE RECYCLEBIN;
  196. */
  197. --hr:
  198. DECLARE
  199.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
  200.   l_message_properties  DBMS_AQ.message_properties_t;
  201.   l_message_handle      RAW(16);
  202.   l_queue_msg           sys.t_event_queue_payload;
  203. BEGIN
  204.   l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');

  205.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
  206.                   enqueue_options     => l_enqueue_options,
  207.                   message_properties  => l_message_properties,
  208.                   payload             => l_queue_msg,
  209.                   msgid               => l_message_handle);
  210.   COMMIT;
  211. END;

  212. select  * from scheduler_test  order by id;
复制代码

job7.sql:

  1. select  * from scheduler_test;

  2. DROP TABLE scheduler_test;

  3. DROP SEQUENCE scheduler_test_seq;

  4. ---
  5. CREATE TABLE scheduler_test (

  6.   id            NUMBER(10)    NOT NULL,

  7.   description   VARCHAR2(20)  NOT NULL,

  8.   created_date  DATE          NOT NULL,

  9.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)

  10. );

  11. CREATE SEQUENCE scheduler_test_seq;

  12. ----

  13. BEGIN
  14.   DBMS_SCHEDULER.create_program (
  15.     program_name   => 'test_program_1',
  16.     program_type   => 'PLSQL_BLOCK',
  17.     program_action => 'BEGIN
  18.                          INSERT INTO scheduler_test (id, description, created_date)
  19.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
  20.                          COMMIT;
  21.                        END;',
  22.     enabled        => TRUE,
  23.     comments       => 'Step2');

  24.   DBMS_SCHEDULER.create_program (
  25.     program_name   => 'test_program_2',
  26.     program_type   => 'PLSQL_BLOCK',
  27.     program_action => 'BEGIN
  28.                          INSERT INTO scheduler_test (id, description, created_date)
  29.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
  30.                          COMMIT;
  31.                        END;',
  32.     enabled        => TRUE,
  33.     comments       => 'Step3');

  34.   DBMS_SCHEDULER.create_program (
  35.     program_name   => 'test_program_3',
  36.     program_type   => 'PLSQL_BLOCK',
  37.     program_action => 'BEGIN
  38.                          INSERT INTO scheduler_test (id, description, created_date)
  39.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
  40.                          COMMIT;
  41.                        END;',
  42.     enabled        => TRUE,
  43.     comments       => 'Step4');
  44. END;


  45. ---


  46. select  * from  user_scheduler_chain_rules;





  47. ---

  48. DECLARE
  49.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
  50.   l_message_properties  DBMS_AQ.message_properties_t;
  51.   l_message_handle      RAW(16);
  52.   l_queue_msg           sys.t_event_queue_payload;
  53. BEGIN
  54.   l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');

  55.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
  56.                   enqueue_options     => l_enqueue_options,
  57.                   message_properties  => l_message_properties,
  58.                   payload             => l_queue_msg,
  59.                   msgid               => l_message_handle);
  60.   COMMIT;
  61. END;

  62. select  * from scheduler_test  order by id;


  63. BEGIN

  64.   DBMS_SCHEDULER.run_chain (

  65.     chain_name    =>  'chain1',

  66.     job_name      =>  'chain1_test_job',

  67.     start_steps   =>  'STEP2, STEP4');

  68. END;
  69. ----

  70. select    s.USERNAME,s.RESOURCE_CONSUMER_GROUP
  71.       from v_$session  s where   username='HR';
  72.       
  73.       
  74.   grant execute on jobclass1 to hr;

复制代码




回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 13:43 , Processed in 0.038892 second(s), 27 queries .

快速回复 返回顶部 返回列表