第33次活动：2017-11-15（星期三晚上7:00-9:30）

botang

1. select  * from dba_scheduler_external_dests;
   
2. 
   
3. DECLARE
   
4. versionnum VARCHAR2(30);
   
5. BEGIN
   
6. versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
   
7. DBMS_OUTPUT.PUT_LINE(versionnum);
   
8. END;
   
9. 
   
10. alter user hr profile default;
    
11. 
    
12. select  * from dba_tab_privs tp where
    
13. tp.grantee='HR';
    
14. 
    
15. grant all on station17 to hr;
    
16. 
    
17. select  * from dict where table_name like 'DBA%DESTS';
    
18. 
    
19. select  * from dba_scheduler_db_dests;
    
20. 
    
21. ----
    
22. 
    
23. begin
    
24.    dbms_scheduler.create_database_destination(destination_name => 'db199utf',agent => 'station199',
    
25.    tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = utforcl.example.com)))');
    
26.    end;
    
27.    
    
28.    ---
    
29.    begin
    
30.    dbms_scheduler.create_database_destination(destination_name => 'db199orcl',agent => 'station199',
    
31.    tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.example.com)))');
    
32. end;
    
33. 
    
34. ----
    
35. begin
    
36.   dbms_scheduler.create_credential(
    
37.         credential_name => 'cred2',
    
38.         username => 'hr',
    
39.         password => 'hr');
    
40. end;
    
41. -----
    
42. 
    
43. select  * from dba_scheduler_db_dests;
    
44. 
    
45. grant execute on cred2 to hr;
    
46. 
    
47. grant all on db199utf to hr;
    
48. grant all on db199orcl to hr;
    
49. 
    
50. /* EM ERROR:
    
51. BEGIN
    
52. sys.dbms_scheduler.create_job(
    
53. job_name => '"HR"."JOB13"',
    
54. job_type => 'PLSQL_BLOCK',
    
55. job_action => 'begin
    
56.   update salary=salary+1 where employee_id=100;
    
57.   commit;
    
58. end;',
    
59. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
    
60. start_date => systimestamp at time zone 'Asia/Shanghai',
    
61. job_class => '"DEFAULT_JOB_CLASS"',
    
62. comments => 'job13',
    
63. auto_drop => FALSE,
    
64. enabled => FALSE);
    
65. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB13"', attribute => 'destination', value => 'SYS.DB199UTF' );
    
66. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB13"', attribute => 'credential_name', value => '"SYS"."CRED2"' );
    
67. sys.dbms_scheduler.enable( '"HR"."JOB13"' );
    
68. END;
    
69. */
    
70. BEGIN
    
71. sys.dbms_scheduler.create_job(
    
72. job_name => '"HR"."JOB131"',
    
73. job_type => 'PLSQL_BLOCK',
    
74. job_action => 'begin
    
75.   update employees set salary=salary+1 where employee_id=100;
    
76.   commit;
    
77. end;',
    
78. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
    
79. start_date => systimestamp at time zone 'Asia/Shanghai',
    
80. job_class => '"DEFAULT_JOB_CLASS"',
    
81. comments => 'job131',
    
82. auto_drop => FALSE,
    
83. enabled => true,
    
84. destination_name => 'SYS.DB199UTF' ,
    
85. credential_name=> '"SYS"."CRED2"'
    
86. );
    
87. END;
    
88. 
    
89. 
    
90. create or replace procedure hr.proc132 is
    
91. begin
    
92.     update employees set salary=salary+1 where employee_id=100;
    
93.     commit;
    
94. end;
    
95. 
    
96. 
    
97. BEGIN
    
98. sys.dbms_scheduler.create_job(
    
99. job_name => '"HR"."JOB132"',
    
100. job_type => 'stored_procedure',
     
101. job_action =>'hr.proc132',
     
102. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
     
103. start_date => systimestamp at time zone 'Asia/Shanghai',
     
104. job_class => '"DEFAULT_JOB_CLASS"',
     
105. comments => 'job132',
     
106. auto_drop => FALSE,
     
107. enabled => true,
     
108. destination_name => 'SYS.DB199ORCL' ,
     
109. credential_name=> '"SYS"."CRED2"'
     
110. );
     
111. END;
     
112. 
     
113. 
     
114. BEGIN
     
115. sys.dbms_scheduler.create_job(
     
116. job_name => '"HR"."JOB133"',
     
117. job_type => 'stored_procedure',
     
118. job_action =>'hr.proc132',
     
119. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
     
120. start_date => systimestamp at time zone 'Asia/Shanghai',
     
121. job_class => '"DEFAULT_JOB_CLASS"',
     
122. comments => 'job133',
     
123. auto_drop => FALSE,
     
124. enabled => true,
     
125. destination_name => 'SYS.DB199ORCL' ,
     
126. credential_name=> '"SYS"."CRED2"'
     
127. );
     
128. END;
     
129. 
     
130. 
     
131. 
     
132. begin
     
133.    dbms_scheduler.create_database_destination(destination_name => 'db17orcl',agent => 'station17',
     
134.    tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))');
     
135.    end;
     
136. 
     
137. grant all on db17orcl to hr;
     
138. 
     
139. BEGIN
     
140. sys.dbms_scheduler.create_job(
     
141. job_name => '"HR"."JOB134"',
     
142. job_type => 'PLSQL_BLOCK',
     
143. job_action => 'begin
     
144.   update employees set salary=salary+1 where employee_id=100;
     
145.   commit;
     
146. end;',
     
147. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
     
148. start_date => systimestamp at time zone 'Asia/Shanghai',
     
149. job_class => '"DEFAULT_JOB_CLASS"',
     
150. comments => 'job134',
     
151. auto_drop => FALSE,
     
152. enabled => true,
     
153. destination_name => 'SYS.DB17ORCL' ,
     
154. credential_name=> '"SYS"."CRED2"'
     
155. );
     
156. END;

复制代码

界面点不了：


group和 group member：

1. 
   
2. select * from dba_rsrc_plans  p
   
3. where p.plan='DEFAULT_PLAN';
   
4. 
   
5. select * from dba_rsrc_plan_directives  p
   
6. where p.plan='DEFAULT_PLAN';
   
7. 
   
8. select * from dba_rsrc_plans  p
   
9. where p.plan='ORA$AUTOTASK_SUB_PLAN';
   
10. 
    
11.   select * from dba_rsrc_plan_directives  p
    
12. where p.plan='ORA$AUTOTASK_SUB_PLAN';

复制代码