文件监视及文件监视job

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) 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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值