课程第12次（2018-11-01星期四）

botang · 发表于 2018-11-1 19:34:49

select * from dba_scheduler_global_attribute;
select * from dba_scheduler_notifications;
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_server','station90.example.com');
end;
begin
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
('email_sender','oracle@example.com');
end;
begin
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB5',
events =>'job_succeeded,job_completed',
recipients =>'oracle@example.com');
end;
begin
DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB5');
end;
错误
create or replace procedure hr.proc1
begin
update t123456 set a=2;
end;
begin
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
job_name =>'HR.JOB51',
recipients =>'oracle@example.com');
end;
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'test_notification_job',
recipients => 'oracle@example.com',
events => 'job_failed',
filter_condition => ':event.error_code=600');
END;
/

复制代码

关于资源消费者组：

BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'HR',
consumer_group => 'GROUP1',
grant_option => FALSE
);
END;
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'HR',
consumer_group => 'GROUP2',
grant_option => FALSE
);
END;
BEGIN
dbms_resource_manager.set_initial_consumer_group(
user => 'HR',
consumer_group => 'GROUP1'
);
END;

复制代码

select s.USERNAME, s.SID, s.SERIAL#,s.STATUS, s.RESOURCE_CONSUMER_GROUP
from v_$session s
where s.USERNAME='HR' and s.TERMINAL='pts/1';
select * from v$transaction;
grant execute on jobclass1 to hr;
grant all on wingroup1 to hr;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB7"',
job_type => 'PLSQL_BLOCK',
job_action => 'declare
v1 number;
begin
select count(*) into v1 from t04209_uname a, t04209_uname b;
end;',
schedule_name => '"SYS"."WINGROUP1"',
job_class => '"JOBCLASS1"',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"HR"."JOB7"', attribute => 'stop_on_window_close', value => TRUE);
sys.dbms_scheduler.enable( '"HR"."JOB7"' );
END;

复制代码

create table t05317_lw( a timestamp ) ;
create or replace procedure proc05317_lw
is
v_1 number;
begin
select count(*) into v_1 from employees a, employees b;
insert into t05317_lw values(systimestamp);
commit;
end;
begin
dbms_scheduler.create_job(
job_name => 'hr.job8',
program_name=>'hr.program8',
schedule_name=>'hr.schedule8',
job_style=>'LIGHTWEIGHT',
enabled => true);
end;
select * from t05317_lw;
truncate table 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;
---------------------------
select count(*) from t05317_lw;
select max(a) - min(a) from t05317_lw;
-------

复制代码

把基于事件的schedule改写：

--HR:
truncate table scheduler_test;
select scheduler_test_seq.currval from dual;
out型参数介绍
create or replace procedure proc_test( p_1 number , p_2 out number)
is
begin
update employees set salary=10000 where employee_id=p_1;
select salary into p_2 from employees where employee_id=p_1;
end;
select * from user_errors;
测试
declare
v_salary number(8,2);
begin
proc_test(100,v_salary);
dbms_output.put_line(v_salary);
end;
in-out型参数介绍
create or replace procedure proc_test_io( p_1 in out number )
is
begin
p_1 := p_1*2;
end;
select * from user_errors;
测试
declare
v_1 number;
begin
v_1 := 1;
proc_test_io( v_1);
dbms_output.put_line(v_1);
end;
alter table scheduler_test add ( event_name varchar2(40)) ;
create or replace procedure procnew( p_1 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;
注：如果创建的程序需要输入参数，则必须定义完参数后在激活，即创建这个program时将enable设为false，否则提示：
Ora-27456：程序“ ”的参数并未全部定义；然后再对该program定义参数即执行define_program_argument过程（见1.3）。
select * from user_scheduler_programs;
select * from user_scheduler_program_args;
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 order by id;
/*
1.3 define_program_argument
参数说明：
program_name——程序名称
argument_position——参数位置（也可以知道参数名称选择argument_name）
argument_type——参数类型
default_value——参数默认值
例子：
BEGIN
dbms_scheduler.define_program_argument(program_name => 'myprogram',
argument_position => 1,
argument_name => 'p_lttid',
argument_type => 'varchar2',
default_value => 'daaa');
END;
--查看定义的program参数
SELECT *FROM User_Scheduler_Program_Args;
1.4 define_anydata_argument（用于定义输入参数为复杂类型需采用sys.AnyData类型来包装的参数）
参数说明：
program_name——程序名称
argument_position——参数位置（也可以知道参数名称选择argument_name）
argument_type——参数类型为sys.AnyData
default_value——参数默认值
1.5 define_metadata_argument
有效的metadata attributes有: 'job_name', 'job_subname', 'job_owner', 'job_start', 'window_start',
'window_end', and 'event_message'.
Metadata Attribute Data Type Description
job_name VARCHAR2 当前执行的job名称
job_subname VARCHAR2 当前执行的job子名称
job_owner VARCHAR2 当前执行的job所有者
job_start TIMESTAMP WITH TIME ZONE job启动的时间
window_start TIMESTAMP WITH TIME ZONE window打开的时间
window_end TIMESTAMP WITH TIME ZONE window关闭的时间
event_message 事件触发job启动的信息
例子：（以下metadata_attribute设置为job_name，即以job_start这个值作为输入参数）
BEGIN
dbms_scheduler.define_metadata_argument(program_name => 'myprogram',
argument_position => 1,
argument_name => 'p_lttid',
metadata_attribute => 'job_start');
END;
1.6 drop_program_argument
例子：
BEGIN
dbms_scheduler.drop_program_argument(program_name => 'myprogram',
argument_position => 1);
END;
*/
begin
dbms_scheduler.define_metadata_argument (
program_name => 'programnew',
argument_position => 1 ,
metadata_attribute => 'EVENT_MESSAGE') ;
end;
select * from user_scheduler_program_args;
begin
dbms_scheduler.enable ('programnew');
end;

复制代码

-------------------

[root@station90 lib64]# ls -l libpam.so.*
lrwxrwxrwx. 1 root root 16 10月 25 23:55 libpam.so.0 -> libpam.so.0.82.2
-rwxr-xr-x. 1 root root 55280 7月 10 2017 libpam.so.0.82.2
[root@station90 lib64]# ln -s libpam.so.0.82.2 libpam.so
[root@station90 lib64]# cd /lib
[root@station90 lib]# ls -l libpam*
lrwxrwxrwx. 1 root root 17 10月 25 23:59 libpamc.so.0 -> libpamc.so.0.82.1
-rwxr-xr-x. 1 root root 10084 7月 10 2017 libpamc.so.0.82.1
lrwxrwxrwx. 1 root root 21 10月 25 23:59 libpam_misc.so.0 -> libpam_misc.so.0.82.0
-rwxr-xr-x. 1 root root 9156 7月 10 2017 libpam_misc.so.0.82.0
lrwxrwxrwx. 1 root root 16 10月 25 23:59 libpam.so.0 -> libpam.so.0.82.2
-rwxr-xr-x. 1 root root 49468 7月 10 2017 libpam.so.0.82.2
[root@station90 lib]# ln -s libpam.so.0.82.2 libpam.so
[root@station90 lib]#

复制代码

远程数据库作业：

--SYS:
select * from dba_scheduler_external_dests;
select * from dba_scheduler_db_dests;
select * from dba_scheduler_dests;
/*
Next we define the database destination. The agent parameter should reference the destination name associated with the remote agent. If the tns_name parameter is NULL, the default instance on the remote host is assumed. This default instance is defined by the ORACLE_SID and ORACLE_HOME parameters in the "schagent.conf" file on the remote server. If a tns_name is specified, it can either be a complete Oracle Net connect descriptor, or a regular alias, but it must be resolvable on the database running the scheduler, not just the remote host.
*/
create_database_destination
创建目标数据库，用于执行远程job
AGENT——The external destination name of the Scheduler agent to connect. Equivalent to an agent name.
The external destination must already exist. The external destination representing an agent is
created automatically on a database instance when the agent registers with that instance.
An agent‘s name is specified in its agent configuration file. If it is not specified, it defaults
to the first part (before the first period) of the name of the host it resides on.
DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION (
destination_name IN VARCHAR2,--目标数据库名称
agent IN VARCHAR2,--代理名称，事先创建好的
tns_name IN VARCHAR2,--tns名称
comments IN VARCHAR2 DEFAULT NULL);
drop_database_destination
drop_agent_destination
begin
dbms_scheduler.create_database_destination(
destination_name => 'db199',
agent => 'station199',tns_name => 'orcl199');
end
begin
dbms_scheduler.create_database_destination(destination_name => 'db199utf',agent => 'station199',
tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = utforcl.example.com)))');
end;
begin
dbms_scheduler.create_database_destination(destination_name => 'db199orcl',agent => 'station199',
tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.example.com)))');
end;
begin
dbms_scheduler.create_credential(
credential_name => 'credential3',
username => 'hr',
password => 'hr');
end;
grant execute on credential3 to hr;
grant all on db199 to hr;
grant all on db199utl to hr;
grant all on db199orcl to hr;
--HR:
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'hr.job13',
job_type => 'STORED_PROCEDURE',
job_action => 'hr.procremotedb_win',
start_date => systimestamp at time zone 'PRC',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job13',
auto_drop => FALSE,
enabled => FALSE,
credential_name => 'sys.credential3',
destination_name => 'db199');
end;
begin
sys.dbms_scheduler.enable( 'hr.job13' );
END;
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'hr.job131',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update employees set salary=salary+1 where employee_id=100;
commit;
end;',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB131',
auto_drop => FALSE,
credential_name =>'"SYS"."CREDENTIAL3"' ,destination_name =>'db199' ,
enabled => true);
END;

复制代码

select * from dba_scheduler_groups g where g.group_name='GROUP1';
select * from dba_scheduler_group_members gm where gm.group_name='GROUP1';
/*
The group_type parameter determines the type of group created (DB_DEST, EXTERNAL_DEST). The member parameter contains a comma separated list of destinations, which must already exist. The syntax for a destinations is as follows.
[[schema.]credential@][schema.]destination
The keyword "LOCAL" can be used to indicate the job should be run on the local machine also. In the case of remote external jobs the LOCAL keyword can be prefixed with a credential, but for database jobs it can not and always runs using the credentials of the owner of the job.
*/
create_group
参数：
group_type——组类型，该组的所有成员必须是同一类型，已有的类型有三种：
DB_DEST：即成员为目标数据库，执行远程数据库的job；
EXTERNAL_DEST（External destination）：Members are external destinations, for running remote external jobs；
WINDOW：Members are Scheduler windows
The inclusion of the credential_name parameter is unnecessary here, but it specifies a default credential to use if any of the destinations in the group were defined without an explicit credential.
创建时可以指定成员也可不指定，添加成员通过add_group_member过程添加。
BEGIN
dbms_scheduler.create_group(group_name =>,
group_type =>,
MEMBER =>,
comments =>);
END;
drop_group
删除组
The ADD_GROUP_MEMBER and REMOVE_GROUP_MEMBER procedures can be used to maintain the member list of the group. The following example adds the destination created in the previous section to the destination group.
BEGIN
DBMS_SCHEDULER.add_group_member(
group_name => 'test_db_group',
member => 'test_credential@rac1_rac1_dest');
END;
/
add_group_member
为组添加成员
remove_group_member
移除组成员
BEGIN
DBMS_SCHEDULER.create_group(
group_name => 'hr.group1',
group_type => 'DB_DEST',member => 'LOCAL,DB199UTF,DB199ORCL'
);
END;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB14"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update employees set salary=9999 where employee_id=100;
commit;
end;',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB14',
auto_drop => FALSE,
credential_name =>'"SYS"."CREDENTIAL3"' ,destination_name =>'HR.GROUP1' ,
enabled => true);
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB141',
job_type => 'stored_procedure',
job_action => 'procgroup',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; bysecond=0',
end_date => SYSTIMESTAMP + 1/24,
credential_name => 'credential3',
destination_name => 'group1',
enabled => TRUE);
END;
/
select * from dba_SCHEDULER_JOB_RUN_DETAILS;
SET LINESIZE 120
COLUMN job_name FORMAT A20
COLUMN destination FORMAT A20
COLUMN actual_start_date FORMAT A20
COLUMN run_duration FORMAT A20
COLUMN status FORMAT A10
SELECT job_name,
destination,
TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
run_duration,
status,
error#
FROM user_scheduler_job_run_details
WHERE job_name = 'MULTI_DEST_JOB'
ORDER BY actual_start_date;
JOB_NAME DESTINATION ACTUAL_START_DATE RUN_DURATION STATUS ERROR#
-------------------- -------------------- -------------------- -------------------- ---------- ----------
MULTI_DEST_JOB LOCAL 01-OCT-2009 11:35:00 +000 00:00:00 SUCCEEDED 0
MULTI_DEST_JOB TEST_DB_GROUP 01-OCT-2009 11:35:00 +000 00:00:03 SUCCEEDED 0
MULTI_DEST_JOB TEST_DB_GROUP 01-OCT-2009 11:36:00 +000 00:00:03 SUCCEEDED 0
MULTI_DEST_JOB LOCAL 01-OCT-2009 11:36:00 +000 00:00:00 SUCCEEDED 0
MULTI_DEST_JOB LOCAL 01-OCT-2009 11:37:00 +000 00:00:00 SUCCEEDED 0
MULTI_DEST_JOB TEST_DB_GROUP 01-OCT-2009 11:37:00 +000 00:00:03 SUCCEEDED 0
MULTI_DEST_JOB RAC1_RAC1_DEST 01-OCT-2009 11:42:34 +000 00:00:02 SUCCEEDED 0
MULTI_DEST_JOB RAC1_RAC1_DEST 01-OCT-2009 11:43:34 +000 00:00:02 SUCCEEDED 0
MULTI_DEST_JOB RAC1_RAC1_DEST 01-OCT-2009 11:44:34 +000 00:00:02 SUCCEEDED 0

复制代码

上完1Z0-053 第17章 Scheduler （8/40）