Bo's Oracle Station

查看: 1993|回复: 0

课程第54次:2016-07-22星期五

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2016-7-23 22:02:07 | 显示全部楼层 |阅读模式
课程第54次:2016-07-22星期五
【继续1Z0-053的第17章】Scheduler

参考别期的帖子:
https://www.botangdb.com/forum.php?mod=viewthread&tid=266&extra=page%3D1

filewatcher:
Screenshot.png

  1. alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=6)';

  2. select  * from dba_users  order by 1;

  3. BEGIN
  4. DBMS_XDB.SETHTTPPORT(8888);
  5. END;

  6. SQL> @?/rdbms/admin/prvtrsch.plb
  7. select  * from dba_users  order by 1;

  8. BEGIN
  9. DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
  10. END;

  11. SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;

  12. --客户机器安装client中的定制---scheduleragent组件(一般填客户机主机名,端口1500)
  13. 客户机需要用schagent -registerdatabase 192.168.0.90 8888 mypassword

  14. --服务器验证:
  15. DECLARE
  16. versionnum VARCHAR2(30);
  17. BEGIN
  18. versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
  19. DBMS_OUTPUT.PUT_LINE(versionnum);
  20. END;

  21. select  * from dba_scheduler_external_dests;


  22. begin
  23.    dbms_scheduler.create_credential('credential1','oracle','oracle');
  24. end;

  25. grant create external job to hr;

  26. grant execute on credential1 to hr;

  27. grant create job to hr;

  28. grant all on station199 to hr;
复制代码

job9.sql:
  1. CREATE TABLE scheduler_test (
  2.   id            NUMBER(10)    NOT NULL,
  3.   created_date  DATE          NOT NULL,
  4.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
  5. );

  6. select  * from scheduler_test;
  7. CREATE SEQUENCE scheduler_test_seq;


  8. ----
  9. alter table scheduler_test add ( event_name  varchar2(40)) ;

  10. ---
  11. create or replace procedure procnew( p_1     sys.t_event_queue_payload    )
  12. is


  13. BEGIN
  14.      INSERT INTO scheduler_test
  15.          VALUES (scheduler_test_seq.NEXTVAL, SYSDATE,  p_1.event_name   );
  16.          COMMIT;
  17. END;

  18. select  * from user_errors;

  19. ---
  20. begin
  21.   dbms_scheduler.create_program (
  22.    program_name => 'programnew',
  23.     program_action=> 'procnew',
  24.     program_type => 'STORED_PROCEDURE',
  25.     number_of_arguments => 1,
  26.     enabled => false) ;
  27. end;

  28. ----

  29. select  * from user_scheduler_programs;

  30. begin
  31.    dbms_scheduler.define_metadata_argument (
  32.     program_name => 'programnew',
  33.     argument_position => 1 ,
  34.    metadata_attribute => 'EVENT_MESSAGE') ;
  35.    
  36. end;

  37. ---

  38. begin
  39. dbms_scheduler.enable ('programnew');
  40. end;




  41. select  * from user_scheduler_program_args;



  42. ---

  43. ----
  44. BEGIN
  45.   -- Create a queue table to hold the event queue.
  46.   DBMS_AQADM.create_queue_table(
  47.     queue_table        => 'event_queue_tab',
  48.     queue_payload_type => 't_event_queue_payload',
  49.     multiple_consumers => TRUE,
  50.     comment            => 'Queue Table For Event Messages');
  51.   -- Create the event queue.
  52.   DBMS_AQADM.create_queue (
  53.     queue_name  => 'event_queue',
  54.     queue_table => 'event_queue_tab');
  55.   -- Start the event queue.
  56.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
  57. END;

  58. ----
  59. begin
  60.    dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
  61. end;
  62.    
  63. begin
  64.    dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
  65. end;
  66. ---

  67. select  * from dba_tab_privs p  where p.grantee='HR';

  68. ---
  69.         [Return]
  70. BEGIN
  71. sys.dbms_scheduler.create_event_schedule(
  72. event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
  73. queue_spec => '"SYS"."EVENT_QUEUE"',
  74. start_date => systimestamp at time zone 'Asia/Shanghai',
  75. schedule_name => '"HR"."SCHEDULE4"');
  76. END;


  77. ----
  78. select  * from hr.scheduler_test;
  79. ----

  80. DECLARE
  81.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
  82.   l_message_properties  DBMS_AQ.message_properties_t;
  83.   l_message_handle      RAW(16);
  84.   l_queue_msg           sys.t_event_queue_payload;
  85. BEGIN
  86.   l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');

  87.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
  88.                   enqueue_options     => l_enqueue_options,
  89.                   message_properties  => l_message_properties,
  90.                   payload             => l_queue_msg,
  91.                   msgid               => l_message_handle);
  92.   COMMIT;
  93. END;







复制代码

job10.sql:

  1. begin
  2.    dbms_scheduler.create_credential('credential1','oracle','oracle');
  3. end;

  4. grant execute on credential1 to hr;

  5. ---

  6. begin
  7.    dbms_scheduler.create_file_watcher(
  8.        file_watcher_name => 'filewatcher1',
  9.        directory_path => '/home/oracle/myfile',
  10.        file_name => 'botang*.txt',
  11.        credential_name => 'credential1');
  12. end;


  13. ---

  14. begin
  15. DBMS_SCHEDULER.enable('filewatcher1');
  16. end;

  17. ---

  18. select  * from dba_scheduler_file_watchers;

  19. ---
  20. grant execute on filewatcher1 to hr;
  21. ---
  22. create table hr.tfilewatcher1 ( a  varchar2(200)  ) ;
  23. ---
  24. create or replace procedure hr.procfilewatcher ( p_1   SYS.SCHEDULER_FILEWATCHER_RESULT)
  25. is
  26. begin
  27.   insert into hr.tfilewatcher1  values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||'      '
  28.                                                       ||p_1.directory_path||'        '
  29.                                                       ||p_1.actual_file_name||'       '
  30.                                                       ||p_1.file_size  ) ;
  31.   commit;
  32. end;
  33. ----
  34. begin
  35.     dbms_scheduler.create_program(
  36.      program_name => 'HR.program10',
  37.      program_type => 'STORED_PROCEDURE',
  38.      program_action => 'HR.procfilewatcher',
  39.      number_of_arguments => 1,
  40.      enabled => false);
  41. end;
  42. ---
  43. begin
  44.    dbms_scheduler.define_metadata_argument(
  45.    program_name => 'HR.program10',
  46.    metadata_attribute => 'event_message',argument_position => 1 ) ;
  47. end;
  48. ---
  49. begin
  50.   dbms_scheduler.enable('HR.program10');
  51. end;

  52. ---
  53. BEGIN
  54.   DBMS_SCHEDULER.CREATE_JOB(
  55.    jOB_NAME=> 'hr.job10',
  56.    PROGRAM_NAME=> 'hr.program10',
  57.    EVENT_CONDITION => 'tab.user_data.file_size > 10',
  58.    QUEUE_SPEC=> 'SYS.filewatcher1',
  59.    AUTO_DROP=> FALSE,
  60.    ENABLED=> true);
  61. END;


  62. begin
  63.    dbms_scheduler.set_attribute('hr.job10','parallel_instances',true);
  64. end;

  65. select  * from  hr.tfilewatcher1;
复制代码

job_array.sql:
  1. create table t05317_lw( a date ) ;

  2. create or replace procedure proc05317_lw
  3. is
  4. begin
  5.    insert into  t05317_lw values(sysdate);
  6.    commit;
  7. end;

  8. select  * from t05317_lw;




  9. DECLARE
  10. newjob sys.job;
  11. newjobarr sys.job_array;
  12. BEGIN
  13. -- Create an array of JOB object types
  14.   newjobarr := sys.job_array();
  15. -- Allocate sufficient space in the array
  16.   newjobarr.extend(100);
  17. -- Add definitions for jobs
  18. FOR i IN 1..100 LOOP
  19.    -- Create a JOB object type
  20.    newjob := sys.job(job_name => 'HR.JOB8_' || to_char(i),
  21.                      job_style => 'LIGHTWEIGHT',
  22.                      job_template => 'HR.PROGRAM8',
  23.                    enabled => TRUE );
  24. -- Add job to the array
  25.    newjobarr(i) := newjob;
  26. END LOOP;
  27. -- Call CREATE_JOBS to create jobs in one transaction
  28. DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
  29. END;


  30. truncate table  t05317_lw;

  31. select  * from  t05317_lw;


  32. begin
  33.    dbms_scheduler.create_job(
  34.    job_name => 'hr.job81',
  35.    program_name=>'hr.program8',
  36.    schedule_name=>'hr.schedule8',
  37.    job_style=>'LIGHTWEIGHT');
  38. end;

  39. select  * from user_scheduler_jobs;


  40. begin
  41.    dbms_scheduler.enable('HR.JOB81');
  42. end;

  43. select  * from hr.t05317_lw;

  44. ----


复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 10:06 , Processed in 0.049224 second(s), 27 queries .

快速回复 返回顶部 返回列表