文件监视及文件监视job

来源：互联网发布：长沙网络私彩彩票编辑：程序博客网时间：2024/04/29 23:50

oracle可以监视文件的接收及生成，然发抛出事件发给job来处理：
在这之前可以更改下监视频率，要在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) ASBEGIN  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.txtSQL> host echo %date% %time% > d:\eod2.txtSQL> host echo %date% %time% > d:\eod3.txtSQL> 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 N2014-08-16 10:17:56.183000    2014-08-16 18:18:15 d:\eod2.txt                  25 N2014-08-16 10:35:23.818000    2014-08-16 18:35:35 d:\eod3.txt                  25 N2014-08-16 10:38:56.254000    2014-08-16 18:39:15 d:\eod4.txt                  25 N4 rows selected

生成文件后过段时间才能有效果，所以要多点耐心

详细内容见官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN13288

0 0