课程第57/58/59次（2017-03-03星期五，2017-03-05星期日上下午）

botang · 发表于 2017-3-6 14:52:18

上完1Z0-053第17章（5个单位时间上完Oracle Scheduler, 做了包括Remote Scheduler的14个实验）
开始性能调优
1Z0-052共19章（上完17章），1Z0-053共21章（上完16章），1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的33章

12c Client 已经可以下载了：ftp://oracle@124.16.180.172
暂时需要密码，密码课堂上提供

2017-03-03.sql:

DROP TABLE hr.scheduler_test;
DROP SEQUENCE hr.scheduler_test_seq;
CREATE TABLE hr.scheduler_test (
id NUMBER(10) NOT NULL,
created_date DATE NOT NULL,
message varchar2(60) ,
CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
);
select * from hr.scheduler_test;
CREATE SEQUENCE hr.scheduler_test_seq;
create or replace procedure hr.procnew( p_1 hr.t_event_queue_payload )
is
BEGIN
INSERT INTO hr.scheduler_test
VALUES (hr.scheduler_test_seq.NEXTVAL, SYSDATE, p_1.event_name );
COMMIT;
END;
begin
dbms_scheduler.create_program (
program_name => 'hr.programnew',
program_action=> 'hr.procnew',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
enabled => false) ;
end;
select spa.ARGUMENT_NAME,
spa.ARGUMENT_POSITION,
spa.ARGUMENT_TYPE,
spa.METADATA_ATTRIBUTE,
spa.PROGRAM_NAME
from dba_scheduler_program_args spa
where spa.OWNER='HR';
begin
dbms_scheduler.define_metadata_argument (
program_name => 'hr.programnew',
argument_position => 1 ,
metadata_attribute => 'EVENT_MESSAGE') ;
end;
begin
dbms_scheduler.enable ('hr.programnew');
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 hr.t_event_queue_payload;
BEGIN
l_queue_msg := hr.t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'hr.event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
----
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB10"',
program_name => '"HR"."PROGRAM10"',
event_condition => 'tab.user_data.file_size > 10',
queue_spec => '"HR"."FILEWATCHER1"',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB10',
auto_drop => FALSE,
enabled => TRUE);
END;
select * from dba_scheduler_credentials;
----
select *
from dba_scheduler_file_watchers fw;
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'hr.filewatcher1',
directory_path => '/home/oracle/mydir',
file_name => 'botang*.txt',
credential_name => 'sys.cred1');
end;
begin
DBMS_SCHEDULER.enable('hr.filewatcher1');
end;
grant execute on SYS.SCHEDULER_FILEWATCHER_RESULT 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.set_attribute('hr.job10','parallel_instances',true);
end;
select * from hr.tfilewatcher1;

复制代码