第60次：2015-06-04星期四

botang

1. select  * from dba_scheduler_credentials;
   
2. 
   
3. begin
   
4.   dbms_scheduler.create_credential(
   
5.         credential_name => 'credlocalfw',
   
6.         username => 'oracle',
   
7.         password => 'oracle');
   
8. end;
   
9. 
   
10. grant execute  on credlocalfw  to hr;
    
11. 
    
12. begin
    
13.    dbms_scheduler.create_file_watcher(
    
14.        file_watcher_name => 'filewlocal',
    
15.        directory_path => '/home/oracle/dirfw',
    
16.        file_name => 'botang*.txt',
    
17.        credential_name => 'credlocalfw');
    
18. end;
    
19. 
    
20. 
    
21. ----------------------------------
    
22. 
    
23. select  * from dba_scheduler_file_watchers;
    
24. 
    
25. grant execute on filewlocal to hr;
    
26. 
    
27. 
    
28. grant execute on SYS.SCHEDULER_FILEWATCHER_RESULT   to hr;
    
29. grant create external job to hr;
    
30. grant create job to hr;
    
31. 
    
32. create table hr.tfilewlocal    ( a  varchar2(200)  ) ;  
    
33. 
    
34. create or replace procedure hr.procfilewlocal ( p_1   SYS.SCHEDULER_FILEWATCHER_RESULT)
    
35. is
    
36. begin
    
37.   insert into     tfilewlocal   values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||'      '
    
38.                                                       ||p_1.directory_path||'        '
    
39.                                                       ||p_1.actual_file_name||'       '
    
40.                                                       ||p_1.file_size  ) ;
    
41.   commit;
    
42. end;
    
43. 
    
44. 
    
45. begin
    
46.     dbms_scheduler.create_program(
    
47.      program_name => 'HR.progfilewlocal',
    
48.      program_type => 'STORED_PROCEDURE',
    
49.      program_action => 'HR.procfilewlocal',
    
50.      number_of_arguments => 1,
    
51.      enabled => false);
    
52. end;
    
53. 
    
54. begin
    
55.    dbms_scheduler.define_metadata_argument(
    
56.    program_name => 'HR.progfilewlocal',
    
57.    metadata_attribute => 'event_message',argument_position => 1 ) ;
    
58. end;
    
59. 
    
60. begin
    
61.   dbms_scheduler.enable('HR.progfilewlocal');
    
62. end;
    
63. 
    
64. begin
    
65.    dbms_scheduler.set_attribute('hr.jobfilewlocal','parallel_instances',true);
    
66. end;
    
67. 
    
68. 
    
69. 
    
70. select  * from  hr.tfilewlocal ;
    
71. 
    
72. 
    
73. -------remote
    
74. begin
    
75.   dbms_scheduler.create_credential(
    
76.         credential_name => 'credremotefw',
    
77.         username => 'botang',
    
78.         password => 'oracle');
    
79. end;
    
80. 
    
81. grant execute  on credremotefw  to hr;
    
82. 
    
83. select  * from dba_scheduler_external_dests;
    
84. 
    
85. begin
    
86.    dbms_scheduler.create_file_watcher(
    
87.    file_watcher_name => 'filewremote',
    
88.    directory_path => 'c:\app\Administrator\dirfw',
    
89.    file_name =>  'botang*.txt',
    
90.    credential_name => 'credremotefw',
    
91.    destination => 'STATION199');
    
92. end;
    
93.    
    
94.   select  * from dba_scheduler_file_watchers;
    
95.    
    
96.   grant execute on filewremote to hr;
    
97.    
    
98.    create table hr.tfilewremote   ( a  varchar2(200)  ) ;  
    
99. 
    
100. create or replace procedure hr.procfilewremote ( p_1   SYS.SCHEDULER_FILEWATCHER_RESULT)
     
101. is
     
102. begin
     
103.   insert into     tfilewremote   values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||'      '
     
104.                                                       ||p_1.directory_path||'        '
     
105.                                                       ||p_1.actual_file_name||'       '
     
106.                                                       ||p_1.file_size  ) ;
     
107.   commit;
     
108. end;
     
109.   
     
110. begin
     
111.     dbms_scheduler.create_program(
     
112.      program_name => 'HR.progfilewremote',
     
113.      program_type => 'STORED_PROCEDURE',
     
114.      program_action => 'HR.procfilewremote',
     
115.      number_of_arguments => 1,
     
116.      enabled => false);
     
117. end;
     
118. 
     
119. begin
     
120.    dbms_scheduler.define_metadata_argument(
     
121.    program_name => 'HR.progfilewremote',
     
122.    metadata_attribute => 'event_message',argument_position => 1 ) ;
     
123. end;
     
124. 
     
125. begin
     
126.   dbms_scheduler.enable('HR.progfilewremote');
     
127. end;
     
128. 
     
129. --EM---job
     
130. BEGIN
     
131.   DBMS_SCHEDULER.CREATE_JOB(
     
132.    jOB_NAME=> 'hr.teacherjob1',
     
133.     PROGRAM_NAME=> 'hr.teacherprog1',
     
134.    EVENT_CONDITION => 'tab.user_data.file_size > 10',
     
135.    QUEUE_SPEC=> 'teacherfilew1',
     
136.    AUTO_DROP=> FALSE,
     
137.   ENABLED=> true);
     
138. END;
     
139. 
     
140. 
     
141. 
     
142. begin
     
143.    dbms_scheduler.set_attribute('hr.jobfilewremote','parallel_instances',true);
     
144. end;
     
145. 
     
146. select * from   hr.tfilewremote;
     
147.       
     
148. select  * from  hr.tfilewlocal;
     
149. 
     
150.   select  * from dba_scheduler_file_watchers;
     
151.   
     
152. -------------------------------
     
153. 
     
154. select  * from dba_scheduler_db_dests;
     
155. 
     
156. begin
     
157.    dbms_scheduler.create_database_destination(
     
158.      destination_name => 'db199',
     
159.      agent => 'station199',tns_name => 'orcl199');
     
160.   end
     
161. 
     
162. begin
     
163.   dbms_scheduler.create_credential(
     
164.         credential_name => 'credremotedb',
     
165.         username => 'hr',
     
166.         password => 'hr');
     
167. end;
     
168. 
     
169. 
     
170. grant execute on credremotedb to hr;
     
171. 
     
172. BEGIN
     
173. sys.dbms_scheduler.create_job(
     
174. job_name => '"HR"."JOBREMOTEDB2"',
     
175. job_type => 'STORED_PROCEDURE',
     
176. job_action => '"HR"."PROC05317WIN"',
     
177. start_date => systimestamp at time zone 'PRC',
     
178. job_class => '"DEFAULT_JOB_CLASS"',
     
179. comments => 'jobremotedb',
     
180. auto_drop => FALSE,
     
181. enabled => FALSE,credential_name => 'sys.credremotedb',destination_name => 'db199');
     
182. end;
     
183. 
     
184. 
     
185. begin
     
186. sys.dbms_scheduler.enable( '"HR"."JOBREMOTEDB2"' );
     
187. END;
     
188. 
     
189. 
     
190. 
     
191. 
     
192. 
     
193. 
     
194. BEGIN
     
195.   DBMS_SCHEDULER.CREATE_JOB(
     
196.    jOB_NAME=> 'hr.teacherjob1',
     
197.     PROGRAM_NAME=> 'hr.teacherprog1',
     
198.    EVENT_CONDITION => 'tab.user_data.file_size > 10',
     
199.    QUEUE_SPEC=> 'teacherfilew1',
     
200.    AUTO_DROP=> FALSE,
     
201.   ENABLED=> true);
     
202. END;
     
203. 
     
204. 
     
205. 
     
206. 
     
207. EGIN
     
208. sys.dbms_scheduler.create_job(
     
209. job_name => '"HR"."JOBFILEW1"',
     
210. program_name => '"HR"."PROGFILEW1"',
     
211. event_condition => 'tab.user_data.file_size > 10',
     
212. queue_spec => '"SYS"."FILEW1"',
     
213. start_date => systimestamp at time zone 'Asia/Shanghai',
     
214. job_class => '"DEFAULT_JOB_CLASS"',
     
215. comments => 'jobfilew1',
     
216. auto_drop => FALSE,
     
217. enabled => TRUE);
     
218. END;
     
219. 
     
220. 
     
221. 
     
222. 
     
223. begin
     
224.    dbms_scheduler.set_attribute('hr.jobfilew1','parallel_instances',true);
     
225. end;
     
226. 
     
227. ---
     
228. 
     
229. grant execute on SYS.SCHEDULER_FILEWATCHER_RESULT   to hr;
     

复制代码