课程第45次（2017-10-17星期二）

botang

HR:

1. DECLARE
   
2.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
   
3.   l_message_properties  DBMS_AQ.message_properties_t;
   
4.   l_message_handle      RAW(16);
   
5.   l_queue_msg           sys.t_event_queue_payload;
   
6. BEGIN
   
7.   l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');
   
8. 
   
9.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
   
10.                   enqueue_options     => l_enqueue_options,
    
11.                   message_properties  => l_message_properties,
    
12.                   payload             => l_queue_msg,
    
13.                   msgid               => l_message_handle);
    
14.   COMMIT;
    
15. END;
    
16. 
    
17. ----------------------
    
18. 
    
19. DROP TABLE scheduler_test;
    
20. DROP SEQUENCE scheduler_test_seq;
    
21. 
    
22. CREATE TABLE scheduler_test (
    
23.   id            NUMBER(10)    NOT NULL,
    
24.   description   VARCHAR2(20)  NOT NULL,
    
25.   created_date  DATE          NOT NULL,
    
26.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
    
27. );
    
28. 
    
29. --------
    
30. CREATE SEQUENCE scheduler_test_seq;
    
31. ------
    
32. BEGIN
    
33.   DBMS_SCHEDULER.create_program (
    
34.     program_name   => 'test_program_1',
    
35.     program_type   => 'PLSQL_BLOCK',
    
36.     program_action => 'BEGIN
    
37.                          INSERT INTO scheduler_test (id, description, created_date)
    
38.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
    
39.                          COMMIT;
    
40.                        END;',
    
41.     enabled        => TRUE,
    
42.     comments       => 'Step2');
    
43. 
    
44.   DBMS_SCHEDULER.create_program (
    
45.     program_name   => 'test_program_2',
    
46.     program_type   => 'PLSQL_BLOCK',
    
47.     program_action => 'BEGIN
    
48.                          INSERT INTO scheduler_test (id, description, created_date)
    
49.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
    
50.                          COMMIT;
    
51.                        END;',
    
52.     enabled        => TRUE,
    
53.     comments       => 'Step3');
    
54. 
    
55.   DBMS_SCHEDULER.create_program (
    
56.     program_name   => 'test_program_3',
    
57.     program_type   => 'PLSQL_BLOCK',
    
58.     program_action => 'BEGIN
    
59.                          INSERT INTO scheduler_test (id, description, created_date)
    
60.                          VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
    
61.                          COMMIT;
    
62.                        END;',
    
63.     enabled        => TRUE,
    
64.     comments       => 'Step4');
    
65. END;
    
66. -------
    
67. 
    
68. 
    

复制代码

EM:

1. BEGIN
   
2. sys.dbms_scheduler.create_chain(
   
3. comments => 'chain1',
   
4. chain_name => '"HR"."CHAIN1"');
   
5. sys.dbms_scheduler.define_chain_event_step(
   
6. chain_name => '"HR"."CHAIN1"',
   
7. step_name => '"STEP1"',
   
8. event_schedule_name => '"HR"."SCHEDULE4"');
   
9. sys.dbms_scheduler.alter_chain(
   
10. chain_name => '"HR"."CHAIN1"',
    
11. step_name => '"STEP1"',
    
12. attribute => 'pause',
    
13. value => FALSE);
    
14. sys.dbms_scheduler.alter_chain(
    
15. chain_name => '"HR"."CHAIN1"',
    
16. step_name => '"STEP1"',
    
17. attribute => 'skip',
    
18. value => FALSE);
    
19. sys.dbms_scheduler.alter_chain(
    
20. chain_name => '"HR"."CHAIN1"',
    
21. step_name => '"STEP1"',
    
22. attribute => 'restart_on_failure',
    
23. value => FALSE);
    
24. sys.dbms_scheduler.define_chain_step(
    
25. chain_name => '"HR"."CHAIN1"',
    
26. step_name => '"STEP2"',
    
27. program_name => '"HR"."TEST_PROGRAM_1"');
    
28. sys.dbms_scheduler.alter_chain(
    
29. chain_name => '"HR"."CHAIN1"',
    
30. step_name => '"STEP2"',
    
31. attribute => 'pause',
    
32. value => FALSE);
    
33. sys.dbms_scheduler.alter_chain(
    
34. chain_name => '"HR"."CHAIN1"',
    
35. step_name => '"STEP2"',
    
36. attribute => 'skip',
    
37. value => FALSE);
    
38. sys.dbms_scheduler.alter_chain(
    
39. chain_name => '"HR"."CHAIN1"',
    
40. step_name => '"STEP2"',
    
41. attribute => 'restart_on_failure',
    
42. value => FALSE);
    
43. sys.dbms_scheduler.define_chain_step(
    
44. chain_name => '"HR"."CHAIN1"',
    
45. step_name => '"STEP3"',
    
46. program_name => '"HR"."TEST_PROGRAM_2"');
    
47. sys.dbms_scheduler.alter_chain(
    
48. chain_name => '"HR"."CHAIN1"',
    
49. step_name => '"STEP3"',
    
50. attribute => 'pause',
    
51. value => FALSE);
    
52. sys.dbms_scheduler.alter_chain(
    
53. chain_name => '"HR"."CHAIN1"',
    
54. step_name => '"STEP3"',
    
55. attribute => 'skip',
    
56. value => FALSE);
    
57. sys.dbms_scheduler.alter_chain(
    
58. chain_name => '"HR"."CHAIN1"',
    
59. step_name => '"STEP3"',
    
60. attribute => 'restart_on_failure',
    
61. value => FALSE);
    
62. sys.dbms_scheduler.define_chain_step(
    
63. chain_name => '"HR"."CHAIN1"',
    
64. step_name => '"STEP4"',
    
65. program_name => '"HR"."TEST_PROGRAM_3"');
    
66. sys.dbms_scheduler.alter_chain(
    
67. chain_name => '"HR"."CHAIN1"',
    
68. step_name => '"STEP4"',
    
69. attribute => 'pause',
    
70. value => FALSE);
    
71. sys.dbms_scheduler.alter_chain(
    
72. chain_name => '"HR"."CHAIN1"',
    
73. step_name => '"STEP4"',
    
74. attribute => 'skip',
    
75. value => FALSE);
    
76. sys.dbms_scheduler.alter_chain(
    
77. chain_name => '"HR"."CHAIN1"',
    
78. step_name => '"STEP4"',
    
79. attribute => 'restart_on_failure',
    
80. value => FALSE);
    
81. sys.dbms_scheduler.define_chain_rule(
    
82. chain_name => '"HR"."CHAIN1"',
    
83. condition => '1=1',
    
84. rule_name => 'RULE1',
    
85. comments => 'rule1',
    
86. action => 'START step1');
    
87. sys.dbms_scheduler.define_chain_rule(
    
88. chain_name => '"HR"."CHAIN1"',
    
89. condition => 'step1 COMPLETED',
    
90. rule_name => 'RULE2',
    
91. comments => 'rule2',
    
92. action => 'START step2');
    
93. sys.dbms_scheduler.define_chain_rule(
    
94. chain_name => '"HR"."CHAIN1"',
    
95. condition => 'step2 COMPLETED',
    
96. rule_name => 'RULE3',
    
97. comments => 'rule3',
    
98. action => 'START step3');
    
99. sys.dbms_scheduler.define_chain_rule(
    
100. chain_name => '"HR"."CHAIN1"',
     
101. condition => 'step3 COMPLETED',
     
102. rule_name => 'RULE4',
     
103. comments => 'rule4',
     
104. action => 'START step4');
     
105. sys.dbms_scheduler.define_chain_rule(
     
106. chain_name => '"HR"."CHAIN1"',
     
107. condition => 'step4 COMPLETED',
     
108. rule_name => 'RULE5',
     
109. comments => 'rule5',
     
110. action => 'END');
     
111. END;
     
112. 
     
113.                         
     

复制代码

附：邮件服务器
/etc/postfix/main.cf

1. [root@station90 postfix]# grep -v '^#'  main.cf | grep -v '^
   
2. 
   
3. 
   
4. queue_directory = /var/spool/postfix
   
5. command_directory = /usr/sbin
   
6. daemon_directory = /usr/libexec/postfix
   
7. data_directory = /var/lib/postfix
   
8. mail_owner = postfix
   
9. myorigin = $mydomain
   
10. inet_interfaces = all
    
11. inet_protocols = all
    
12. mydestination = $myhostname, localhost.$mydomain, localhost, example.com, something.net
    
13. unknown_local_recipient_reject_code = 550
    
14. mynetworks = 192.168.0.0/24, 127.0.0.0/8
    
15. alias_maps = hash:/etc/aliases
    
16. alias_database = hash:/etc/aliases
    
17. 
    
18.   
    
19. debug_peer_level = 2
    
20. debugger_command =
    
21.          PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
    
22.          ddd $daemon_directory/$process_name $process_id & sleep 5
    
23. sendmail_path = /usr/sbin/sendmail.postfix
    
24. newaliases_path = /usr/bin/newaliases.postfix
    
25. mailq_path = /usr/bin/mailq.postfix
    
26. setgid_group = postdrop
    
27. html_directory = no
    
28. manpage_directory = /usr/share/man
    
29. sample_directory = /usr/share/doc/postfix-2.6.6/samples
    
30. readme_directory = /usr/share/doc/postfix-2.6.6/README_FILES
    
31. smtpd_recipient_restrictions= hash:/etc/postfix/access2,
    
32.                               permit_mynetworks,
    
33.                               permit_sasl_authenticated,
    
34.                               reject_unauth_destination
    
35. smtpd_sender_restrictions= hash:/etc/postfix/access,
    
36.                               permit_mynetworks,
    
37.                               permit_sasl_authenticated,
    
38.                               reject_unauth_destination
    
39. [root@station90 postfix]#
    

复制代码

/etc/dovecot/dovecot.conf:

1. login_trusted_networks =  192.168.0.0/24
   
2. dict {
   
3.   #quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
   
4.   #expire = sqlite:/etc/dovecot/dovecot-dict-sql.conf.ext
   
5. }
   
6. !include conf.d/*.conf
   

复制代码

/etc/dovecot/conf.d/10-mail.conf:

1. mail_location = mbox:~/mail:INBOX=/var/mail/%u

复制代码

建用户：

1. [root@station90 ~]# passwd   testzhang3
   
2. 更改用户 testzhang3 的密码 。
   
3. 新的 密码：
   
4. 无效的密码： 它基于字典单词
   
5. 无效的密码： 过于简单
   
6. 重新输入新的 密码：
   
7. passwd： 所有的身份验证令牌已经成功更新。
   
8. [root@station90 ~]# su - testzhang3
   
9. [testzhang3@station90 ~]$ mkdir -p  mail/.imap/INBOX
   
10. [testzhang3@station90 ~]$
    

复制代码

1. select  * from dba_scheduler_global_attribute;
   
2. select  * from dba_scheduler_notifications;
   
3. 
   
4. 
   
5. begin
   
6. DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
   
7. ('email_server','station90.example.com');
   
8. end;
   
9. 
   
10. begin
    
11.   DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
    
12.   ('email_sender','testzhang3@example.com');
    
13. end;
    
14. 
    
15. select  * from dba_scheduler_global_attribute;
    
16. 
    
17. ---
    
18. 
    
19. begin
    
20.   DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    
21.   job_name       =>'HR.JOB5',
    
22.     events   =>'job_failed',
    
23.   recipients     =>'testzhang3@example.com');
    
24. end;
    
25. 
    
26. select  * from dba_scheduler_notifications;

复制代码

1. select  * from dba_scheduler_global_attribute;
   
2. select  * from dba_scheduler_notifications;
   
3. 
   
4. begin
   
5. DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
   
6. ('email_server','station90.example.com');
   
7. end;
   
8. 
   
9. begin
   
10.   DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
    
11.   ('email_sender','oracle@example.com');
    
12. end;
    
13. 
    
14. begin
    
15.   DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    
16.   job_name       =>'HR.JOB5',
    
17.     events   =>'job_succeeded,job_completed',
    
18.   recipients     =>'oracle@example.com');
    
19. end;
    
20. 
    
21. begin
    
22. DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
    
23.   job_name         =>'HR.JOB5');
    
24. end;
    
25. 
    
26. 错误
    
27. create or replace procedure hr.proc1
    
28. begin
    
29.   update t123456 set a=2;
    
30. end;
    
31. 
    
32. begin
    
33. DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
    
34.   job_name       =>'HR.JOB51',
    
35.   recipients     =>'oracle@example.com');
    
36. end;
    
37. 
    
38. BEGIN
    
39. DBMS_SCHEDULER.add_job_email_notification (
    
40.   job_name         =>  'test_notification_job',
    
41.   recipients       =>  'oracle@example.com',
    
42.   events           =>  'job_failed',
    
43.   filter_condition => ':event.error_code=600');
    
44. END;
    
45. /
    
46. 
    
47. 
    
48. 
    

复制代码