|
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。
---------
- select username from cdb_users where username='HR';
- select * from cdb_role_privs rp where rp.grantee='HR';
- select * from dba_sys_privs p where p.grantee='HR';
- grant create external job to hr;
- select * from cdb_scheduler_credentials;
- begin
- dbms_scheduler.create_credential('credential1','oracle','oracle');
- end;
- grant create job to hr;
- select * from dba_tab_privs p where p.grantee='HR';
- grant execute on credential1 to hr;
复制代码
基于事件的schedule:
SYS:
- select * from dba_queues q where q.QUEUE_TYPE='NORMAL_QUEUE' ;
- select * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
- select * from dba_queues q where q.QUEUE_TYPE not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');
- -------------
- CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
- event_name VARCHAR2(30)
- );
- grant execute on t_event_queue_payload to hr;
- -------------------
- BEGIN
- -- Create a queue table to hold the event queue.
- DBMS_AQADM.create_queue_table(
- queue_table => 'event_queue_tab',
- queue_payload_type => 't_event_queue_payload',
- multiple_consumers => TRUE,
- comment => 'Queue Table For Event Messages');
- -- Create the event queue.
- DBMS_AQADM.create_queue (
- queue_name => 'event_queue',
- queue_table => 'event_queue_tab');
- -- Start the event queue.
- DBMS_AQADM.start_queue (queue_name => 'event_queue');
- END;
- begin
- dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
- end;
-
- begin
- dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
- end;
- select * from dba_tab_privs p where p.grantee='HR';
- ----------------
- grant execute on dbms_aq to hr;
复制代码
- CREATE TABLE scheduler_test (
- id NUMBER(10) NOT NULL,
- created_date DATE NOT NULL,
- event_name varchar2(40),
- CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
- );
- CREATE SEQUENCE scheduler_test_seq;
- create or replace procedure procnew( p_1 sys.t_event_queue_payload )
- is
- BEGIN
- INSERT INTO scheduler_test
- VALUES (scheduler_test_seq.NEXTVAL, SYSDATE, p_1.event_name );
- COMMIT;
- END;
- begin
- dbms_scheduler.create_program (
- program_name => 'programnew',
- program_action=> 'procnew',
- program_type => 'STORED_PROCEDURE',
- number_of_arguments => 1,
- enabled => false) ;
- end;
- select * from user_scheduler_programs;
- select * from user_scheduler_program_args;
- begin
- dbms_scheduler.define_metadata_argument (
- program_name => 'programnew',
- argument_position => 1 ,
- metadata_attribute => 'EVENT_MESSAGE') ;
- end;
- select * from user_scheduler_program_args;
- begin
- dbms_scheduler.enable ('programnew');
- end;
-
-
- BEGIN
- sys.dbms_scheduler.create_job(
- job_name => '"HR"."JOB4"',
- program_name => '"HR"."PROGRAMNEW"',
- schedule_name => '"HR"."SCHEDULE4"',
- job_class => '"DEFAULT_JOB_CLASS"',
- comments => 'job4',
- auto_drop => TRUE,
- enabled => TRUE);
- END;
- SELECT * FROM scheduler_test order by id;
- 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('Your are a supper man');
- 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;
- SELECT * FROM scheduler_test order by id;
复制代码
|
|