Bo's Oracle Station

查看: 1997|回复: 0

课程第52次:2016-07-18星期一

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2016-7-19 11:20:35 | 显示全部楼层 |阅读模式
课程第52次:2016-07-18星期一
【开始1Z0-053的第17章】Scheduler
Screenshot.png

Screenshot2.png

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

昨天晚上的随堂SQL:

  1. select  * from dba_scheduler_jobs;

  2. select  * from dba_scheduler_running_jobs;

  3. select  * from dba_scheduler_job_run_details;

  4. ---
  5. select  * from dba_sys_privs p where p.grantee='HR';

  6. grant create external job to hr;
  7. grant create job to hr;
  8. ----
  9. select  * from dba_scheduler_credentials;
  10. begin
  11.    dbms_scheduler.create_credential('credential1','oracle','oracle');
  12. end;

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

  14. grant execute on credential1 to hr;

  15. ----
  16. select  j.state  from dba_scheduler_jobs   j  where   j.job_name='JOB2';

  17. ----


  18. BEGIN
  19. /* Complex scheduling example 1: Public Holidays */
  20. DBMS_SCHEDULER.CREATE_SCHEDULE
  21. (schedule_name => 'SCHEDULE3'
  22. ,start_date => to_timestamp_tz('01/01/2016 00:00:00','dd/mm/yyyy hh24:mi:ss')
  23. ,end_date => to_timestamp_tz('31/12/2016 23:59:59','dd/mm/yyyy hh24:mi:ss')
  24. ,repeat_interval =>'freq=YEARLY;bydate=20160807,20160808,20160901'
  25. ,comments => 'Public Holidays 2016'
  26. );
  27. END;

  28. ---


  29. BEGIN
  30. /* Complex scheduling example 2: Working Days */
  31. DBMS_SCHEDULER.CREATE_SCHEDULE
  32. (schedule_name => 'SCHEDULE31'
  33. ,repeat_interval =>
  34. 'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;'
  35. ,comments => 'Run on every working day'
  36. );
  37. END;
  38. ---
  39. declare
  40.    v_1  timestamp;
  41. begin
  42.   for i in 1..30
  43.    loop
  44.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (    calendar_string=>'FREQ=yearly;bydate=0807,0808,0901;BYHOUR=14;BYMINUTE=10;BYSECOND=0;intersect=HR.SCHEDULE31'   ,
  45.    start_date => sysdate,
  46.    return_date_after  => v_1,
  47.    next_run_date    => v_1 );
  48.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
  49. end loop;
  50. end;

  51. ----
  52. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;

  53. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';

  54. select  * from dba_queues q where q.QUEUE_TYPE   not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');
  55. ---

  56. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
  57.   event_name  VARCHAR2(30)
  58. );

  59. grant execute on t_event_queue_payload to hr;

  60. BEGIN
  61.   -- Create a queue table to hold the event queue.
  62.   DBMS_AQADM.create_queue_table(
  63.     queue_table        => 'event_queue_tab',
  64.     queue_payload_type => 't_event_queue_payload',
  65.     multiple_consumers => TRUE,
  66.     comment            => 'Queue Table For Event Messages');
  67.   -- Create the event queue.
  68.   DBMS_AQADM.create_queue (
  69.     queue_name  => 'event_queue',
  70.     queue_table => 'event_queue_tab');
  71.   -- Start the event queue.
  72.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
  73. END;

  74. ----

  75. begin
  76.    dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
  77. end;

  78. begin
  79.    dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
  80. end;


  81. select  * from dba_tab_privs p  where p.grantee='HR';
  82.    
  83. ---
  84. grant execute on dbms_aq to hr;
  85. ---

  86. DROP TABLE scheduler_test;
  87. ----

  88. CREATE TABLE scheduler_test (
  89.   id            NUMBER(10)    NOT NULL,
  90.   created_date  DATE          NOT NULL,
  91.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
  92. );
  93. ---
  94. select  * from scheduler_test;
  95. CREATE SEQUENCE scheduler_test_seq;

  96. ----

  97. BEGIN

  98.    DBMS_SCHEDULER.create_job (

  99.       job_name        => 'job4',

  100.       job_type        => 'PLSQL_BLOCK',

  101.       job_action      => 'BEGIN

  102.                             INSERT INTO scheduler_test (id, created_date)

  103.                             VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);

  104.                             COMMIT;

  105.                           END;',

  106.       start_date      => SYSTIMESTAMP,

  107.       event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',

  108.       queue_spec      => 'sys.event_queue',

  109.       enabled         => TRUE);

  110. END;

  111. ---


  112. DECLARE
  113.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
  114.   l_message_properties  DBMS_AQ.message_properties_t;
  115.   l_message_handle      RAW(16);
  116.   l_queue_msg           sys.t_event_queue_payload;
  117. BEGIN
  118.   l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');

  119.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
  120.                   enqueue_options     => l_enqueue_options,
  121.                   message_properties  => l_message_properties,
  122.                   payload             => l_queue_msg,
  123.                   msgid               => l_message_handle);
  124.   COMMIT;
  125. END;


  126. select  * from scheduler_test;
复制代码



回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 03:30 , Processed in 0.066252 second(s), 36 queries .

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