课程第54次：2016-07-22星期五

botang · 发表于 2016-7-23 22:02:07

课程第54次：2016-07-22星期五
【继续1Z0-053的第17章】Scheduler

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

filewatcher：

alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=6)';
select * from dba_users order by 1;
BEGIN
DBMS_XDB.SETHTTPPORT(8888);
END;
SQL> @?/rdbms/admin/prvtrsch.plb
select * from dba_users order by 1;
BEGIN
DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
END;
SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
--客户机器安装client中的定制---scheduleragent组件（一般填客户机主机名，端口1500）
客户机需要用schagent -registerdatabase 192.168.0.90 8888 mypassword
--服务器验证：
DECLARE
versionnum VARCHAR2(30);
BEGIN
versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
DBMS_OUTPUT.PUT_LINE(versionnum);
END;
select * from dba_scheduler_external_dests;
begin
dbms_scheduler.create_credential('credential1','oracle','oracle');
end;
grant create external job to hr;
grant execute on credential1 to hr;
grant create job to hr;
grant all on station199 to hr;

复制代码

job9.sql：

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;
----
alter table scheduler_test add ( event_name varchar2(40)) ;
---
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;
select * from user_errors;
---
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;
begin
dbms_scheduler.define_metadata_argument (
program_name => 'programnew',
argument_position => 1 ,
metadata_attribute => 'EVENT_MESSAGE') ;
end;
---
begin
dbms_scheduler.enable ('programnew');
end;
select * from user_scheduler_program_args;
---
----
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';
---
[Return]
BEGIN
sys.dbms_scheduler.create_event_schedule(
event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
queue_spec => '"SYS"."EVENT_QUEUE"',
start_date => systimestamp at time zone 'Asia/Shanghai',
schedule_name => '"HR"."SCHEDULE4"');
END;
----
select * from hr.scheduler_test;
----
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;

复制代码

job10.sql：

begin
dbms_scheduler.create_credential('credential1','oracle','oracle');
end;
grant execute on credential1 to hr;
---
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'filewatcher1',
directory_path => '/home/oracle/myfile',
file_name => 'botang*.txt',
credential_name => 'credential1');
end;
---
begin
DBMS_SCHEDULER.enable('filewatcher1');
end;
---
select * from dba_scheduler_file_watchers;
---
grant execute on filewatcher1 to hr;
---
create table hr.tfilewatcher1 ( a varchar2(200) ) ;
---
create or replace procedure hr.procfilewatcher ( p_1 SYS.SCHEDULER_FILEWATCHER_RESULT)
is
begin
insert into hr.tfilewatcher1 values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||' '
||p_1.directory_path||' '
||p_1.actual_file_name||' '
||p_1.file_size ) ;
commit;
end;
----
begin
dbms_scheduler.create_program(
program_name => 'HR.program10',
program_type => 'STORED_PROCEDURE',
program_action => 'HR.procfilewatcher',
number_of_arguments => 1,
enabled => false);
end;
---
begin
dbms_scheduler.define_metadata_argument(
program_name => 'HR.program10',
metadata_attribute => 'event_message',argument_position => 1 ) ;
end;
---
begin
dbms_scheduler.enable('HR.program10');
end;
---
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
jOB_NAME=> 'hr.job10',
PROGRAM_NAME=> 'hr.program10',
EVENT_CONDITION => 'tab.user_data.file_size > 10',
QUEUE_SPEC=> 'SYS.filewatcher1',
AUTO_DROP=> FALSE,
ENABLED=> true);
END;
begin
dbms_scheduler.set_attribute('hr.job10','parallel_instances',true);
end;
select * from hr.tfilewatcher1;

复制代码

job_array.sql：

create table t05317_lw( a date ) ;
create or replace procedure proc05317_lw
is
begin
insert into t05317_lw values(sysdate);
commit;
end;
select * from t05317_lw;
DECLARE
newjob sys.job;
newjobarr sys.job_array;
BEGIN
-- Create an array of JOB object types
newjobarr := sys.job_array();
-- Allocate sufficient space in the array
newjobarr.extend(100);
-- Add definitions for jobs
FOR i IN 1..100 LOOP
-- Create a JOB object type
newjob := sys.job(job_name => 'HR.JOB8_' || to_char(i),
job_style => 'LIGHTWEIGHT',
job_template => 'HR.PROGRAM8',
enabled => TRUE );
-- Add job to the array
newjobarr(i) := newjob;
END LOOP;
-- Call CREATE_JOBS to create jobs in one transaction
DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
END;
truncate table t05317_lw;
select * from t05317_lw;
begin
dbms_scheduler.create_job(
job_name => 'hr.job81',
program_name=>'hr.program8',
schedule_name=>'hr.schedule8',
job_style=>'LIGHTWEIGHT');
end;
select * from user_scheduler_jobs;
begin
dbms_scheduler.enable('HR.JOB81');
end;
select * from hr.t05317_lw;
----

复制代码