课程第52次：2016-07-18星期一

botang · 发表于 2016-7-19 11:20:35

课程第52次：2016-07-18星期一
【开始1Z0-053的第17章】Scheduler

参考别期的帖子：
https://www.botangdb.com/forum.php?mod=viewthread&tid=266&extra=page%3D1

昨天晚上的随堂SQL：

select * from dba_scheduler_jobs;
select * from dba_scheduler_running_jobs;
select * from dba_scheduler_job_run_details;
---
select * from dba_sys_privs p where p.grantee='HR';
grant create external job to hr;
grant create job to hr;
----
select * from dba_scheduler_credentials;
begin
dbms_scheduler.create_credential('credential1','oracle','oracle');
end;
select * from dba_tab_privs p where p.grantee='HR';
grant execute on credential1 to hr;
----
select j.state from dba_scheduler_jobs j where j.job_name='JOB2';
----
BEGIN
/* Complex scheduling example 1: Public Holidays */
DBMS_SCHEDULER.CREATE_SCHEDULE
(schedule_name => 'SCHEDULE3'
,start_date => to_timestamp_tz('01/01/2016 00:00:00','dd/mm/yyyy hh24:mi:ss')
,end_date => to_timestamp_tz('31/12/2016 23:59:59','dd/mm/yyyy hh24:mi:ss')
,repeat_interval =>'freq=YEARLY;bydate=20160807,20160808,20160901'
,comments => 'Public Holidays 2016'
);
END;
---
BEGIN
/* Complex scheduling example 2: Working Days */
DBMS_SCHEDULER.CREATE_SCHEDULE
(schedule_name => 'SCHEDULE31'
,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..30
loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING ( calendar_string=>'FREQ=yearly;bydate=0807,0808,0901;BYHOUR=14;BYMINUTE=10;BYSECOND=0;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;
----
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;
---
DROP TABLE scheduler_test;
----
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;
----
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'job4',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
INSERT INTO scheduler_test (id, created_date)
VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);
COMMIT;
END;',
start_date => SYSTIMESTAMP,
event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
queue_spec => 'sys.event_queue',
enabled => TRUE);
END;
---
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;

复制代码