|
- select * from dba_tab_privs tp
- where tp.grantee='HR';
-
- grant create job to hr;
-
- select * from dba_sys_privs sp
- where sp.grantee='HR';
-
- begin
- dbms_scheduler.create_credential(credential_name => 'CRED2',
- username => 'oracle',
- password => 'oracle');
- end;
-
- grant create external job to hr;
-
- select * from dba_scheduler_credentials;
-
- grant execute on cred2 to hr;
-
- select * from dba_tab_privs tp
- where tp.grantee='HR';
-
- ---------------
-
- BEGIN
- /* Complex scheduling example 1: Public Holidays */
- DBMS_SCHEDULER.CREATE_SCHEDULE
- (schedule_name => 'HR.SCHEDULE3'
- ,start_date =>sysdate
- ,repeat_interval =>'freq=YEARLY;bydate=0607,0608,0701'
- ,comments => 'Public Holidays 2015'
- );
- END;
- ---
- declare
- v_1 timestamp;
- begin
- for i in 1..10
- loop
- DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
- calendar_string=>'HR.SCHEDULE3' ,
- 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
- /* Complex scheduling example 2: Working Days */
- DBMS_SCHEDULER.CREATE_SCHEDULE
- (schedule_name => 'HR.SCHEDULE31'
- ,start_date => sysdate
- ,repeat_interval =>
- 'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;'
- ,comments => 'Run on every working day'
- );
- END;
- declare
- v_1 timestamp;
- begin
- for i in 1..10
- loop
- DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
- calendar_string=>'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;
- -----
- declare
- v_1 timestamp;
- begin
- for i in 1..10
- loop
- DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
- calendar_string=>'HR.SCHEDULE3;intersect=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
- sys.dbms_scheduler.set_attribute( name => '"HR"."SCHEDULE3"', attribute => 'repeat_interval', value => 'FREQ=YEARLY;BYYEARDAY=-208,-207,-184;BYHOUR=10;BYMINUTE=10;BYSECOND=10');
- END;
- ----
- declare
- v_1 timestamp;
- begin
- for i in 1..10
- loop
- DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
- calendar_string=>'HR.SCHEDULE3;intersect=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;
- -----
- declare
- v_1 timestamp;
- begin
- for i in 1..10
- loop
- DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
- calendar_string=>'FREQ=YEARLY;BYYEARDAY=-208,-207,-184;BYHOUR=10;BYMINUTE=10;BYSECOND=10;intersect=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
- /* Complex scheduling example 1: Public Holidays */
- DBMS_SCHEDULER.CREATE_SCHEDULE
- (schedule_name => 'HR.SCHEDULE32'
- ,start_date =>sysdate
- ,repeat_interval =>'FREQ=YEARLY;BYYEARDAY=-208,-207,-184;BYHOUR=10;BYMINUTE=10;BYSECOND=10;intersect=HR.SCHEDULE31'
- ,comments => 'Public Holidays 2015'
- );
- 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;
复制代码
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)
- );
-
- 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;
- ---
- 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');
- 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';
复制代码 HR:
- 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;
- ----------------
复制代码
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;
- SELECT owner,
- job_name,
- chain_owner,
- chain_name,
- step_name,
- state
- FROM dba_scheduler_running_chains
- ORDER BY owner, job_name, chain_name, step_name;
- select * from hr.scheduler_test order by 1 ;
- ------------------------------
- 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;
复制代码
|
|