Bo's Oracle Station

查看: 2710|回复: 0

第28次活动:2017-10-30(星期一晚上7:00-9:30)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-10-30 19:57:35 | 显示全部楼层 |阅读模式
  1. select  * from dba_tab_privs tp
  2. where tp.grantee='HR';

  3. grant  create job to hr;

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

  6. begin
  7.    dbms_scheduler.create_credential(credential_name => 'CRED2',
  8.    username => 'oracle',
  9.    password => 'oracle');
  10.   end;
  11.       
  12.   grant create external job to hr;
  13.    
  14. select  * from dba_scheduler_credentials;
  15.   
  16. grant execute on cred2 to hr;

  17. select  * from dba_tab_privs tp
  18. where tp.grantee='HR';

  19. ---------------

  20. BEGIN
  21. /* Complex scheduling example 1: Public Holidays */
  22. DBMS_SCHEDULER.CREATE_SCHEDULE
  23. (schedule_name => 'HR.SCHEDULE3'
  24. ,start_date =>sysdate

  25. ,repeat_interval =>'freq=YEARLY;bydate=0607,0608,0701'
  26. ,comments => 'Public Holidays 2015'
  27. );
  28. END;
  29. ---
  30. declare
  31.    v_1  timestamp;
  32. begin
  33.   for i in 1..10
  34.    loop
  35.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (  
  36.           calendar_string=>'HR.SCHEDULE3'   ,
  37.    start_date =>sysdate,
  38.    return_date_after  => v_1,
  39.    next_run_date    => v_1 );
  40.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
  41. end loop;
  42. end;
复制代码

Screenshot.png


  1. BEGIN
  2. /* Complex scheduling example 2: Working Days */
  3. DBMS_SCHEDULER.CREATE_SCHEDULE
  4. (schedule_name => 'HR.SCHEDULE31'
  5. ,start_date => sysdate

  6. ,repeat_interval =>
  7. 'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;'
  8. ,comments => 'Run on every working day'
  9. );
  10. END;

  11. declare
  12.    v_1  timestamp;
  13. begin
  14.   for i in 1..10
  15.    loop
  16.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (  
  17.           calendar_string=>'HR.SCHEDULE31'   ,
  18.    start_date =>sysdate,
  19.    return_date_after  => v_1,
  20.    next_run_date    => v_1 );
  21.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
  22. end loop;
  23. end;

  24. -----
  25. declare
  26.    v_1  timestamp;
  27. begin
  28.   for i in 1..10
  29.    loop
  30.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (  
  31.           calendar_string=>'HR.SCHEDULE3;intersect=HR.SCHEDULE31 '   ,
  32.    start_date =>sysdate,
  33.    return_date_after  => v_1,
  34.    next_run_date    => v_1 );
  35.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
  36. end loop;
  37. end;
  38. ----
  39. BEGIN
  40. sys.dbms_scheduler.set_attribute( name => '"HR"."SCHEDULE3"', attribute => 'repeat_interval', value => 'FREQ=YEARLY;BYYEARDAY=-208,-207,-184;BYHOUR=10;BYMINUTE=10;BYSECOND=10');
  41. END;

  42. ----
  43. declare
  44.    v_1  timestamp;
  45. begin
  46.   for i in 1..10
  47.    loop
  48.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (  
  49.           calendar_string=>'HR.SCHEDULE3;intersect=HR.SCHEDULE31 '   ,
  50.    start_date =>sysdate,
  51.    return_date_after  => v_1,
  52.    next_run_date    => v_1 );
  53.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
  54. end loop;
  55. end;

  56. -----
  57. declare
  58.    v_1  timestamp;
  59. begin
  60.   for i in 1..10
  61.    loop
  62.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (  
  63.           calendar_string=>'FREQ=YEARLY;BYYEARDAY=-208,-207,-184;BYHOUR=10;BYMINUTE=10;BYSECOND=10;intersect=HR.SCHEDULE31 '   ,
  64.    start_date =>sysdate,
  65.    return_date_after  => v_1,
  66.    next_run_date    => v_1 );
  67.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
  68. end loop;
  69. end;

  70. ----
  71. BEGIN
  72. /* Complex scheduling example 1: Public Holidays */
  73. DBMS_SCHEDULER.CREATE_SCHEDULE
  74. (schedule_name => 'HR.SCHEDULE32'
  75. ,start_date =>sysdate

  76. ,repeat_interval =>'FREQ=YEARLY;BYYEARDAY=-208,-207,-184;BYHOUR=10;BYMINUTE=10;BYSECOND=10;intersect=HR.SCHEDULE31'
  77. ,comments => 'Public Holidays 2015'
  78. );
  79. END;

  80. ----
  81. declare
  82.    v_1  timestamp;
  83. begin
  84.   for i in 1..10
  85.    loop
  86.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (  
  87.           calendar_string=>'HR.SCHEDULE32 '   ,
  88.    start_date =>sysdate,
  89.    return_date_after  => v_1,
  90.    next_run_date    => v_1 );
  91.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
  92. end loop;
  93. end;
复制代码

job4:

  1. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;

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


  3. select  * from dba_queues q where q.QUEUE_TYPE  
  4. not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');


  5. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
  6.   event_name  VARCHAR2(30)
  7. );

  8. grant execute on t_event_queue_payload to hr;

  9. BEGIN
  10.   -- Create a queue table to hold the event queue.
  11.   DBMS_AQADM.create_queue_table(
  12.     queue_table        => 'event_queue_tab',
  13.     queue_payload_type => 't_event_queue_payload',
  14.     multiple_consumers => TRUE,
  15.     comment            => 'Queue Table For Event Messages');
  16.   -- Create the event queue.
  17.   DBMS_AQADM.create_queue (
  18.     queue_name  => 'event_queue',
  19.     queue_table => 'event_queue_tab');
  20.   -- Start the event queue.
  21.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
  22. END;
  23. ---
  24. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;

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


  26. select  * from dba_queues q where q.QUEUE_TYPE  
  27. not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');

  28. begin
  29.    dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
  30. end;
  31.    
  32. begin
  33.    dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
  34. end;

  35. -----------------

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

复制代码
HR:
  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. ----------------
复制代码
Screenshot-2.png Screenshota.png
DECLARE
  l_enqueue_options     DBMS_AQ.enqueue_options_t;
  l_message_properties  DBMS_AQ.message_properties_t;
  l_message_handle      RAW(16);
  l_queue_msg           sys.t_event_queue_payload;
BEGIN
  l_queue_msg := sys.t_event_queue_payload('OhMyGod');

  DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
                  enqueue_options     => l_enqueue_options,
                  message_properties  => l_message_properties,
                  payload             => l_queue_msg,
                  msgid               => l_message_handle);
  COMMIT;
END;

  1. SELECT owner,

  2.        job_name,

  3.        chain_owner,

  4.        chain_name,

  5.        step_name,

  6.        state

  7. FROM   dba_scheduler_running_chains

  8. ORDER BY owner, job_name, chain_name, step_name;


  9. select  * from hr.scheduler_test  order by 1 ;

  10. ------------------------------

  11. DECLARE
  12.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
  13.   l_message_properties  DBMS_AQ.message_properties_t;
  14.   l_message_handle      RAW(16);
  15.   l_queue_msg           sys.t_event_queue_payload;
  16. BEGIN
  17.   l_queue_msg := sys.t_event_queue_payload('OhMyGod');

  18.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
  19.                   enqueue_options     => l_enqueue_options,
  20.                   message_properties  => l_message_properties,
  21.                   payload             => l_queue_msg,
  22.                   msgid               => l_message_handle);
  23.   COMMIT;
  24. END;
复制代码

Screenshot-1.png
回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 21:33 , Processed in 0.044212 second(s), 27 queries .

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