Bo's Oracle Station

查看: 2269|回复: 0

课程第57次(2018-06-06星期三)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-6-6 19:54:59 | 显示全部楼层 |阅读模式
Windows上做外部作业要注意:
1. 打开组策略,用gpedit.msc命令。
2. 逐步找到compute configuration -- windows setting -- security setting -- local policy
-- user rights assignment -- log on as a batch job
3.双击"log on as a batch job",在这里加入你需要的操作系统user
4.再次在OEM的Host Credentials输入那些user和password,一切ok。

---------

  1. select username from cdb_users where username='HR';

  2. select  * from cdb_role_privs rp where rp.grantee='HR';

  3. select  * from dba_sys_privs p where p.grantee='HR';

  4. grant create external job to hr;

  5. select  * from cdb_scheduler_credentials;


  6. begin
  7.    dbms_scheduler.create_credential('credential1','oracle','oracle');
  8. end;

  9. grant create job to hr;

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

  11. grant execute on credential1 to hr;



复制代码

基于事件的schedule:
SYS:
  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   not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');

  4. -------------
  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. -------------------
  10. BEGIN
  11.   -- Create a queue table to hold the event queue.
  12.   DBMS_AQADM.create_queue_table(
  13.     queue_table        => 'event_queue_tab',
  14.     queue_payload_type => 't_event_queue_payload',
  15.     multiple_consumers => TRUE,
  16.     comment            => 'Queue Table For Event Messages');
  17.   -- Create the event queue.
  18.   DBMS_AQADM.create_queue (
  19.     queue_name  => 'event_queue',
  20.     queue_table => 'event_queue_tab');
  21.   -- Start the event queue.
  22.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
  23. END;


  24. begin
  25.    dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
  26. end;
  27.    
  28. begin
  29.    dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
  30. end;

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

  32. ----------------
  33. grant execute on dbms_aq to hr;

复制代码

schedule4.png


schedule41.png

  1. CREATE TABLE scheduler_test (
  2.   id            NUMBER(10)    NOT NULL,
  3.   created_date  DATE          NOT NULL,
  4.   event_name  varchar2(40),
  5.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
  6. );

  7. CREATE SEQUENCE scheduler_test_seq;


  8. create or replace procedure procnew( p_1     sys.t_event_queue_payload    )
  9. is

  10. BEGIN
  11.      INSERT INTO scheduler_test
  12.          VALUES (scheduler_test_seq.NEXTVAL, SYSDATE,  p_1.event_name   );
  13.          COMMIT;
  14. END;


  15. begin
  16.   dbms_scheduler.create_program (
  17.    program_name => 'programnew',
  18.     program_action=> 'procnew',
  19.     program_type => 'STORED_PROCEDURE',
  20.     number_of_arguments => 1,
  21.     enabled => false) ;
  22. end;

  23. select  * from user_scheduler_programs;
  24. select  * from user_scheduler_program_args;


  25. begin
  26.    dbms_scheduler.define_metadata_argument (
  27.     program_name => 'programnew',
  28.     argument_position => 1 ,
  29.    metadata_attribute => 'EVENT_MESSAGE') ;
  30. end;

  31. select  * from user_scheduler_program_args;


  32. begin
  33. dbms_scheduler.enable ('programnew');
  34. end;

  35.        
  36.                        
  37. BEGIN
  38. sys.dbms_scheduler.create_job(
  39. job_name => '"HR"."JOB4"',
  40. program_name => '"HR"."PROGRAMNEW"',
  41. schedule_name => '"HR"."SCHEDULE4"',
  42. job_class => '"DEFAULT_JOB_CLASS"',
  43. comments => 'job4',
  44. auto_drop => TRUE,
  45. enabled => TRUE);
  46. END;


  47. SELECT * FROM scheduler_test order by id;


  48. DECLARE
  49.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
  50.   l_message_properties  DBMS_AQ.message_properties_t;
  51.   l_message_handle      RAW(16);
  52.   l_queue_msg           sys.t_event_queue_payload;
  53. BEGIN
  54.   l_queue_msg := sys.t_event_queue_payload('Your are a supper man');

  55.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
  56.                   enqueue_options     => l_enqueue_options,
  57.                   message_properties  => l_message_properties,
  58.                   payload             => l_queue_msg,
  59.                   msgid               => l_message_handle);
  60.   COMMIT;
  61. END;

  62. SELECT * FROM scheduler_test order by id;
复制代码


Screenshot-Error.png

回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

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

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