oracle可以监视文件的接收及生成,然发抛出事件发给job来处理:
在这之前可以更改下监视频率,要在sys下更改:
监视及job
间断的生成文件:
可以通过数据字典来查看job是否执行成功,要有点耐心
可以看到生成文件及job生成数据的时间:
生成文件后过段时间才能有效果,所以要多点耐心
详细内容见官方文档: http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN13288
在这之前可以更改下监视频率,要在sys下更改:
BEGIN
dbms_scheduler.set_attribute('FILE_WATCHER_SCHEDULE',
'REPEAT_INTERVAL',
'FREQ=SECONDLY;INTERVAL=10');
END;
本例中改为10秒
监视及job
BEGIN
dbms_scheduler.purge_log;
END;
/
BEGIN
dbms_scheduler.drop_job(job_name => 'eod_job');
END;
/
BEGIN
dbms_scheduler.drop_program(program_name => 'eod_prog');
END;
/
BEGIN
dbms_scheduler.drop_file_watcher(file_watcher_name => 'eod_reports_watcher');
END;
/
BEGIN
dbms_scheduler.drop_credential(credential_name => 'watch_credential');
END;
/
DROP table eod_reports PURGE;
create table eod_reports
(
when timestamp,
file_name varchar2(100),
file_size number,
created DATE,
processed char(1)
);
CREATE OR REPLACE PROCEDURE q_eod_report(payload IN sys.scheduler_filewatcher_result) AS
BEGIN
INSERT INTO eod_reports
VALUES
(payload.file_timestamp,
payload.directory_path || payload.actual_file_name,
payload.file_size,
SYSDATE,
'N');
COMMIT;
END;
/
BEGIN
/*创建身份证明*/
dbms_scheduler.create_credential(credential_name => 'watch_credential',
username => 'administrator',
password => 'uiqyds');
/*program*/
dbms_scheduler.create_program(program_name => 'eod_prog',
program_type => 'stored_procedure',
program_action => 'q_eod_report',
number_of_arguments => 1,
enabled => FALSE);
/*增加参数*/
dbms_scheduler.define_metadata_argument(program_name => 'eod_prog',
metadata_attribute => 'event_message',
argument_position => 1);
/*创建文件监视*/
dbms_scheduler.create_file_watcher(file_watcher_name => 'eod_reports_watcher',
directory_path => 'd:\',
file_name => 'eod*.txt',
credential_name => 'watch_credential',
destination => NULL,
steady_state_duration => '0 00:00:10',
enabled => FALSE);
/*创建job前program必须要可用*/
dbms_scheduler.enable('eod_prog');
/*创建job*/
dbms_scheduler.create_job(job_name => 'eod_job',
program_name => 'eod_prog',
event_condition => 'tab.user_data.file_size > 10',
queue_spec => 'eod_reports_watcher',
auto_drop => FALSE,
enabled => FALSE);
dbms_scheduler.set_attribute('eod_job', 'parallel_instances', TRUE);
dbms_scheduler.enable('eod_reports_watcher,eod_job');
END;
/
间断的生成文件:
SQL> host echo %date% %time% > d:\eod1.txt
SQL> host echo %date% %time% > d:\eod2.txt
SQL> host echo %date% %time% > d:\eod3.txt
SQL> host echo %date% %time% > d:\eod4.txt
可以通过数据字典来查看job是否执行成功,要有点耐心
SELECT *
FROM Dba_Scheduler_Job_Log d
WHERE job_name = upper('eod_job');
可以看到生成文件及job生成数据的时间:
SQL> SELECT to_char(WHEN, 'yyyy-mm-dd hh24:mi:ss.ff') AS WHEN,
2 to_char(created, 'yyyy-mm-dd hh24:mi:ss') AS created,
3 file_name,
4 file_size,
5 processed
6 FROM eod_reports
7 ORDER BY 1;
WHEN CREATED FILE_NAME FILE_SIZE PROCESSED
----------------------------- ------------------- -------------------- ---------- ---------
2014-08-16 10:17:13.098000 2014-08-16 18:17:26 d:\eod1.txt 25 N
2014-08-16 10:17:56.183000 2014-08-16 18:18:15 d:\eod2.txt 25 N
2014-08-16 10:35:23.818000 2014-08-16 18:35:35 d:\eod3.txt 25 N
2014-08-16 10:38:56.254000 2014-08-16 18:39:15 d:\eod4.txt 25 N
4 rows selected
生成文件后过段时间才能有效果,所以要多点耐心
详细内容见官方文档: http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN13288