文件监视及文件监视job

最新推荐文章于 2021-07-06 10:26:37 发布

jgmydsai

最新推荐文章于 2021-07-06 10:26:37 发布

阅读量1.2k

点赞数

分类专栏： DBMS_SCHEDULER 文章标签： create_file_watcher create_job scheduler_filewatche

本文链接：https://blog.csdn.net/jgmydsai/article/details/38615441

版权

DBMS_SCHEDULER 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

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