第28次活动：2017-10-30（星期一晚上7:00-9:30）
select* from dba_tab_privs tpwhere tp.grantee='HR';
grantcreate 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_1timestamp;
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_1timestamp;
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_1timestamp;
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_1timestamp;
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_1timestamp;
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_1timestamp;
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 qwhereq.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_nameVARCHAR2(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 qwhereq.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 pwhere p.grantee='HR';
HR:
CREATE TABLE scheduler_test (
id NUMBER(10) NOT NULL,
created_dateDATE 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_propertiesDBMS_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_testorder by 1 ;
------------------------------
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_propertiesDBMS_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]