|
job复杂时间类型:
- BEGIN
- DBMS_SCHEDULER.CREATE_SCHEDULE
- (schedule_name => 'HR.SCHEDULE3'
- ,repeat_interval =>'freq=YEARLY;bydate=20171021,20171117,20171231'
- ,comments => 'schedule3'
- );
- END;
- ----
- BEGIN
- DBMS_SCHEDULER.CREATE_SCHEDULE
- (schedule_name => 'HR.SCHEDULE31'
- ,repeat_interval =>'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;'
- ,comments => 'SCHEDULE31'
- );
- END;
- ---
- declare
- v_1 timestamp;
- begin
- for i in 1..10
- loop
- DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
- calendar_string=>'freq=YEARLY;bydate=1021,1117,1231;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31' ,
- start_date => sysdate,
- return_date_after => v_1,
- next_run_date => v_1 );
- dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
- end loop;
- end;
- ----
- BEGIN
- DBMS_SCHEDULER.CREATE_SCHEDULE
- (schedule_name => 'HR.SCHEDULE32'
- ,repeat_interval =>'freq=YEARLY;bydate=1021,1117,1231;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31'
- ,comments => 'schedule32'
- );
- END;
- ---
- declare
- v_1 timestamp;
- begin
- for i in 1..10
- loop
- DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
- calendar_string=>'HR.SCHEDULE32' ,
- start_date => sysdate,
- return_date_after => v_1,
- next_run_date => v_1 );
- dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
- end loop;
- end;
复制代码
sys的窗口 job4:
- 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)
- );
- ---
- 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;
- ----
- 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');
- ----
- select * from dba_tab_privs p where p.grantee='HR';
- ----
- 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;
- ----
- select * from dba_tab_privs p where p.grantee='HR';
- -----
复制代码
hr的窗口job4:
- DROP TABLE scheduler_test;
- DROP SEQUENCE scheduler_test_seq;
- CREATE TABLE scheduler_test (
- id NUMBER(10) NOT NULL,
- created_date DATE NOT NULL,
- CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
- );
- select * from scheduler_test;
- CREATE SEQUENCE scheduler_test_seq;
复制代码
grant execute on sys.t_event_queue_payload to hr;
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('give_me_a_prod');
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;
|
|