Bo's Oracle Station

查看: 2134|回复: 0

课程第12次(2018-11-01星期四)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-11-1 19:34:49 | 显示全部楼层 |阅读模式
email_status.png

  1. select  * from dba_scheduler_global_attribute;
  2. select  * from dba_scheduler_notifications;

  3. begin
  4. DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
  5. ('email_server','station90.example.com');
  6. end;

  7. begin
  8.   DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
  9.   ('email_sender','oracle@example.com');
  10. end;

  11. begin
  12.   DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  13.   job_name       =>'HR.JOB5',
  14.     events   =>'job_succeeded,job_completed',
  15.   recipients     =>'oracle@example.com');
  16. end;

  17. begin
  18. DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
  19.   job_name         =>'HR.JOB5');
  20. end;

  21. 错误
  22. create or replace procedure hr.proc1
  23. begin
  24.   update t123456 set a=2;
  25. end;

  26. begin
  27. DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
  28.   job_name       =>'HR.JOB51',
  29.   recipients     =>'oracle@example.com');
  30. end;

  31. BEGIN
  32. DBMS_SCHEDULER.add_job_email_notification (
  33.   job_name         =>  'test_notification_job',
  34.   recipients       =>  'oracle@example.com',
  35.   events           =>  'job_failed',
  36.   filter_condition => ':event.error_code=600');
  37. END;
  38. /

复制代码
关于资源消费者组:
  1.         BEGIN
  2.     dbms_resource_manager_privs.grant_switch_consumer_group(
  3.         grantee_name => 'HR',
  4.         consumer_group => 'GROUP1',
  5.         grant_option => FALSE
  6.     );
  7. END;
  8. BEGIN
  9.     dbms_resource_manager_privs.grant_switch_consumer_group(
  10.         grantee_name => 'HR',
  11.         consumer_group => 'GROUP2',
  12.         grant_option => FALSE
  13.     );
  14. END;
  15. BEGIN
  16.     dbms_resource_manager.set_initial_consumer_group(
  17.         user => 'HR',
  18.         consumer_group => 'GROUP1'
  19.     );
  20. END;
复制代码
  1. select s.USERNAME, s.SID, s.SERIAL#,s.STATUS, s.RESOURCE_CONSUMER_GROUP
  2. from v_$session s
  3. where s.USERNAME='HR' and s.TERMINAL='pts/1';

  4. select  * from v$transaction;

  5. grant execute on jobclass1 to hr;

  6. grant all on wingroup1 to hr;

  7. BEGIN
  8. sys.dbms_scheduler.create_job(
  9. job_name => '"HR"."JOB7"',
  10. job_type => 'PLSQL_BLOCK',
  11. job_action => 'declare
  12.   v1 number;
  13. begin
  14.    select  count(*)  into v1 from t04209_uname a, t04209_uname b;
  15. end;',
  16. schedule_name => '"SYS"."WINGROUP1"',
  17. job_class => '"JOBCLASS1"',
  18. auto_drop => FALSE,
  19. enabled => FALSE);
  20. sys.dbms_scheduler.set_attribute( name => '"HR"."JOB7"', attribute => 'stop_on_window_close', value => TRUE);
  21. sys.dbms_scheduler.enable( '"HR"."JOB7"' );
  22. END;
复制代码
  1. create table t05317_lw( a timestamp ) ;

  2. create or replace procedure proc05317_lw
  3. is
  4. v_1 number;
  5. begin
  6.    select count(*) into v_1 from employees a, employees b;
  7.    insert into  t05317_lw values(systimestamp);
  8.    commit;
  9. end;


  10. begin
  11.    dbms_scheduler.create_job(
  12.    job_name => 'hr.job8',
  13.    program_name=>'hr.program8',
  14.    schedule_name=>'hr.schedule8',
  15.    job_style=>'LIGHTWEIGHT',
  16.    enabled => true);
  17. end;

  18. select  * from t05317_lw;

  19. truncate table t05317_lw;


  20. --------------------------
  21. DECLARE
  22. newjob sys.job;
  23. newjobarr sys.job_array;
  24. BEGIN
  25. -- Create an array of JOB object types
  26.   newjobarr := sys.job_array();
  27. -- Allocate sufficient space in the array
  28.   newjobarr.extend(100);
  29. -- Add definitions for jobs
  30. FOR i IN 1..100 LOOP
  31.    -- Create a JOB object type
  32.    newjob := sys.job(job_name => 'HR.JOB8_' || to_char(i),
  33.                      job_style => 'LIGHTWEIGHT',
  34.                      job_template => 'HR.PROGRAM8',
  35.                    enabled => TRUE );
  36. -- Add job to the array
  37.    newjobarr(i) := newjob;
  38. END LOOP;
  39. -- Call CREATE_JOBS to create jobs in one transaction
  40. DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
  41. END;

  42. ---------------------------

  43. select count(*) from t05317_lw;

  44. select max(a) - min(a) from t05317_lw;

  45. -------
复制代码

把基于事件的schedule改写:
  1. --HR:
  2. truncate table scheduler_test;
  3. select scheduler_test_seq.currval from dual;

  4. out型参数介绍
  5. create or replace procedure proc_test( p_1  number , p_2  out   number)
  6. is
  7. begin
  8.   update employees set salary=10000 where employee_id=p_1;
  9.   select  salary into p_2 from  employees  where employee_id=p_1;
  10. end;

  11. select  * from user_errors;
  12. 测试
  13. declare
  14.   v_salary  number(8,2);
  15. begin
  16.   proc_test(100,v_salary);
  17.   dbms_output.put_line(v_salary);
  18. end;

  19. in-out型参数介绍
  20. create or replace procedure  proc_test_io( p_1  in out number )  
  21. is
  22. begin
  23.   p_1 := p_1*2;
  24. end;

  25. select  * from user_errors;
  26. 测试
  27. declare
  28.   v_1 number;
  29. begin
  30.    v_1 := 1;
  31.    proc_test_io( v_1);
  32.    dbms_output.put_line(v_1);
  33. end;

  34. alter table scheduler_test add ( event_name  varchar2(40)) ;

  35. create or replace procedure procnew( p_1     t_event_queue_payload    )
  36. is

  37. BEGIN
  38.      INSERT INTO scheduler_test
  39.          VALUES (scheduler_test_seq.NEXTVAL, SYSDATE,  p_1.event_name   );
  40.          COMMIT;
  41. END;

  42. select  * from user_errors;

  43. begin
  44.   dbms_scheduler.create_program (
  45.    program_name => 'programnew',
  46.     program_action=> 'procnew',
  47.     program_type => 'STORED_PROCEDURE',
  48.     number_of_arguments => 1,
  49.     enabled => false) ;
  50. end;
  51. 注:如果创建的程序需要输入参数,则必须定义完参数后在激活,即创建这个program时将enable设为false,否则提示:
  52.     Ora-27456:程序“ ”的参数并未全部定义;然后再对该program定义参数即执行define_program_argument过程(见1.3)。
  53. select  * from user_scheduler_programs;
  54. select  * from user_scheduler_program_args;

  55. DECLARE
  56.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
  57.   l_message_properties  DBMS_AQ.message_properties_t;
  58.   l_message_handle      RAW(16);
  59.   l_queue_msg           sys.t_event_queue_payload;
  60. BEGIN
  61.   l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');

  62.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
  63.                   enqueue_options     => l_enqueue_options,
  64.                   message_properties  => l_message_properties,
  65.                   payload             => l_queue_msg,
  66.                   msgid               => l_message_handle);
  67.   COMMIT;
  68. END;

  69. SELECT * FROM scheduler_test order by id;
  70. /*
  71. 1.3 define_program_argument
  72. 参数说明:
  73. program_name——程序名称
  74. argument_position——参数位置(也可以知道参数名称选择argument_name)
  75. argument_type——参数类型
  76. default_value——参数默认值

  77. 例子:
  78. BEGIN
  79.    dbms_scheduler.define_program_argument(program_name      => 'myprogram',
  80.                                           argument_position => 1,
  81.                                           argument_name     => 'p_lttid',
  82.                                           argument_type     => 'varchar2',
  83.                                           default_value     => 'daaa');
  84. END;

  85. --查看定义的program参数   
  86. SELECT *FROM User_Scheduler_Program_Args;

  87. 1.4 define_anydata_argument(用于定义输入参数为复杂类型需采用sys.AnyData类型来包装的参数)
  88. 参数说明:
  89. program_name——程序名称
  90. argument_position——参数位置(也可以知道参数名称选择argument_name)
  91. argument_type——参数类型为sys.AnyData
  92. default_value——参数默认值


  93. 1.5 define_metadata_argument
  94. 有效的metadata attributes有: 'job_name', 'job_subname', 'job_owner', 'job_start', 'window_start',
  95.                              'window_end', and 'event_message'.
  96. Metadata Attribute              Data Type                              Description
  97. job_name                        VARCHAR2                               当前执行的job名称
  98. job_subname                     VARCHAR2                               当前执行的job子名称
  99. job_owner                       VARCHAR2                               当前执行的job所有者
  100. job_start                   TIMESTAMP WITH TIME ZONE                   job启动的时间
  101. window_start                TIMESTAMP WITH TIME ZONE                   window打开的时间
  102. window_end                  TIMESTAMP WITH TIME ZONE                   window关闭的时间
  103. event_message                                                          事件触发job启动的信息

  104. 例子:(以下metadata_attribute设置为job_name,即以job_start这个值作为输入参数)
  105. BEGIN
  106.    dbms_scheduler.define_metadata_argument(program_name       => 'myprogram',
  107.                                            argument_position  => 1,
  108.                                            argument_name      => 'p_lttid',
  109.                                            metadata_attribute => 'job_start');
  110. END;

  111. 1.6 drop_program_argument
  112. 例子:
  113. BEGIN
  114.    dbms_scheduler.drop_program_argument(program_name      => 'myprogram',
  115.                                         argument_position => 1);
  116. END;

  117. */

  118. begin
  119.    dbms_scheduler.define_metadata_argument (
  120.     program_name => 'programnew',
  121.     argument_position => 1 ,
  122.    metadata_attribute => 'EVENT_MESSAGE') ;
  123.    
  124. end;

  125. select  * from user_scheduler_program_args;

  126. begin
  127. dbms_scheduler.enable ('programnew');
  128. end;

复制代码

-------------------
  1. [root@station90 lib64]# ls -l libpam.so.*
  2. lrwxrwxrwx. 1 root root    16 10月 25 23:55 libpam.so.0 -> libpam.so.0.82.2
  3. -rwxr-xr-x. 1 root root 55280  7月 10 2017 libpam.so.0.82.2
  4. [root@station90 lib64]# ln -s  libpam.so.0.82.2  libpam.so
  5. [root@station90 lib64]# cd /lib
  6. [root@station90 lib]# ls -l libpam*
  7. lrwxrwxrwx. 1 root root    17 10月 25 23:59 libpamc.so.0 -> libpamc.so.0.82.1
  8. -rwxr-xr-x. 1 root root 10084  7月 10 2017 libpamc.so.0.82.1
  9. lrwxrwxrwx. 1 root root    21 10月 25 23:59 libpam_misc.so.0 -> libpam_misc.so.0.82.0
  10. -rwxr-xr-x. 1 root root  9156  7月 10 2017 libpam_misc.so.0.82.0
  11. lrwxrwxrwx. 1 root root    16 10月 25 23:59 libpam.so.0 -> libpam.so.0.82.2
  12. -rwxr-xr-x. 1 root root 49468  7月 10 2017 libpam.so.0.82.2
  13. [root@station90 lib]# ln -s libpam.so.0.82.2 libpam.so
  14. [root@station90 lib]#
复制代码

远程数据库作业:
  1. --SYS:
  2. select  * from dba_scheduler_external_dests;
  3. select  * from dba_scheduler_db_dests;
  4. select * from dba_scheduler_dests;

  5. /*
  6. 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.
  7. */

  8. create_database_destination
  9. 创建目标数据库,用于执行远程job
  10. AGENT——The external destination name of the Scheduler agent to connect. Equivalent to an agent name.
  11.        The external destination must already exist. The external destination representing an agent is
  12.        created automatically on a database instance when the agent registers with that instance.
  13.        An agent‘s name is specified in its agent configuration file. If it is not specified, it defaults
  14.        to the first part (before the first period) of the name of the host it resides on.

  15. DBMS_SCHEDULER.CREATE_DATABASE_DESTINATION (
  16.    destination_name        IN VARCHAR2,--目标数据库名称
  17.    agent                   IN VARCHAR2,--代理名称,事先创建好的
  18.    tns_name                IN VARCHAR2,--tns名称
  19.    comments                IN VARCHAR2 DEFAULT NULL);

  20. drop_database_destination
  21. drop_agent_destination

  22. begin
  23.    dbms_scheduler.create_database_destination(
  24.    destination_name => 'db199',
  25.    agent => 'station199',tns_name => 'orcl199');
  26. end

  27. begin
  28.    dbms_scheduler.create_database_destination(destination_name => 'db199utf',agent => 'station199',
  29.    tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = utforcl.example.com)))');
  30.    end;
  31.    
  32. begin
  33.    dbms_scheduler.create_database_destination(destination_name => 'db199orcl',agent => 'station199',
  34.    tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.example.com)))');
  35. end;

  36. begin
  37.   dbms_scheduler.create_credential(
  38.         credential_name => 'credential3',
  39.         username => 'hr',
  40.         password => 'hr');
  41. end;

  42. grant execute on credential3 to hr;
  43. grant all on db199 to hr;
  44. grant all on db199utl to hr;
  45. grant all on db199orcl to hr;

  46. --HR:
  47. BEGIN
  48. sys.dbms_scheduler.create_job(
  49. job_name => 'hr.job13',
  50. job_type => 'STORED_PROCEDURE',
  51. job_action => 'hr.procremotedb_win',
  52. start_date => systimestamp at time zone 'PRC',
  53. job_class => '"DEFAULT_JOB_CLASS"',
  54. comments => 'job13',
  55. auto_drop => FALSE,
  56. enabled => FALSE,
  57. credential_name => 'sys.credential3',
  58. destination_name => 'db199');
  59. end;

  60. begin
  61. sys.dbms_scheduler.enable( 'hr.job13' );
  62. END;


  63. BEGIN
  64. sys.dbms_scheduler.create_job(
  65. job_name => 'hr.job131',
  66. job_type => 'PLSQL_BLOCK',
  67. job_action => 'begin
  68.    update employees set salary=salary+1  where employee_id=100;
  69.    commit;
  70. end;',
  71. start_date => systimestamp at time zone 'Asia/Shanghai',
  72. job_class => '"DEFAULT_JOB_CLASS"',
  73. comments => 'JOB131',
  74. auto_drop => FALSE,
  75. credential_name =>'"SYS"."CREDENTIAL3"' ,destination_name =>'db199' ,
  76. enabled => true);
  77. END;
复制代码
  1. select  * from dba_scheduler_groups g where g.group_name='GROUP1';
  2. select  * from dba_scheduler_group_members gm where gm.group_name='GROUP1';

  3. /*
  4. 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.

  5.     [[schema.]credential@][schema.]destination

  6. 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.
  7. */

  8. create_group
  9. 参数:
  10. group_type——组类型,该组的所有成员必须是同一类型,已有的类型有三种:
  11.             DB_DEST:即成员为目标数据库,执行远程数据库的job;
  12.             EXTERNAL_DEST(External destination):Members are external destinations, for running remote external jobs;
  13.             WINDOW:Members are Scheduler windows
  14. 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.
  15. 创建时可以指定成员也可不指定,添加成员通过add_group_member过程添加。
  16. BEGIN
  17.    dbms_scheduler.create_group(group_name =>,
  18.                                group_type =>,
  19.                                MEMBER     =>,
  20.                                comments   =>);
  21. END;

  22. drop_group
  23. 删除组

  24. 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.

  25. BEGIN
  26.   DBMS_SCHEDULER.add_group_member(
  27.   group_name    => 'test_db_group',
  28.   member        => 'test_credential@rac1_rac1_dest');
  29. END;
  30. /
  31. add_group_member
  32. 为组添加成员

  33. remove_group_member
  34. 移除组成员

  35. BEGIN
  36.   DBMS_SCHEDULER.create_group(
  37.     group_name    => 'hr.group1',
  38.     group_type    => 'DB_DEST',member => 'LOCAL,DB199UTF,DB199ORCL'
  39.     );
  40. END;

  41. BEGIN
  42. sys.dbms_scheduler.create_job(
  43. job_name => '"HR"."JOB14"',
  44. job_type => 'PLSQL_BLOCK',
  45. job_action => 'begin
  46.    update employees set salary=9999  where employee_id=100;
  47.    commit;
  48. end;',
  49. start_date => systimestamp at time zone 'Asia/Shanghai',
  50. job_class => '"DEFAULT_JOB_CLASS"',
  51. comments => 'JOB14',
  52. auto_drop => FALSE,
  53. credential_name =>'"SYS"."CREDENTIAL3"' ,destination_name =>'HR.GROUP1' ,
  54. enabled => true);
  55. END;

  56. BEGIN
  57.    DBMS_SCHEDULER.CREATE_JOB (
  58.        job_name            =>  'JOB141',
  59.        job_type            =>  'stored_procedure',
  60.        job_action          =>  'procgroup',
  61.        start_date          =>  SYSTIMESTAMP,
  62.        repeat_interval     =>  'freq=minutely; bysecond=0',
  63.        end_date            =>  SYSTIMESTAMP + 1/24,
  64.        credential_name     =>  'credential3',
  65.        destination_name    =>  'group1',
  66.        enabled             =>  TRUE);
  67. END;
  68. /

  69. select  *  from  dba_SCHEDULER_JOB_RUN_DETAILS;

  70.     SET LINESIZE 120
  71.     COLUMN job_name FORMAT A20
  72.     COLUMN destination FORMAT A20
  73.     COLUMN actual_start_date FORMAT A20
  74.     COLUMN run_duration FORMAT A20
  75.     COLUMN status FORMAT A10

  76.     SELECT job_name,
  77.            destination,
  78.            TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
  79.            run_duration,
  80.            status,
  81.            error#
  82.     FROM   user_scheduler_job_run_details
  83.     WHERE  job_name = 'MULTI_DEST_JOB'
  84.     ORDER BY actual_start_date;

  85.     JOB_NAME             DESTINATION          ACTUAL_START_DATE    RUN_DURATION         STATUS         ERROR#
  86.     -------------------- -------------------- -------------------- -------------------- ---------- ----------
  87.     MULTI_DEST_JOB       LOCAL                01-OCT-2009 11:35:00 +000 00:00:00        SUCCEEDED           0
  88.     MULTI_DEST_JOB       TEST_DB_GROUP        01-OCT-2009 11:35:00 +000 00:00:03        SUCCEEDED           0
  89.     MULTI_DEST_JOB       TEST_DB_GROUP        01-OCT-2009 11:36:00 +000 00:00:03        SUCCEEDED           0
  90.     MULTI_DEST_JOB       LOCAL                01-OCT-2009 11:36:00 +000 00:00:00        SUCCEEDED           0
  91.     MULTI_DEST_JOB       LOCAL                01-OCT-2009 11:37:00 +000 00:00:00        SUCCEEDED           0
  92.     MULTI_DEST_JOB       TEST_DB_GROUP        01-OCT-2009 11:37:00 +000 00:00:03        SUCCEEDED           0
  93.     MULTI_DEST_JOB       RAC1_RAC1_DEST       01-OCT-2009 11:42:34 +000 00:00:02        SUCCEEDED           0
  94.     MULTI_DEST_JOB       RAC1_RAC1_DEST       01-OCT-2009 11:43:34 +000 00:00:02        SUCCEEDED           0
  95.     MULTI_DEST_JOB       RAC1_RAC1_DEST       01-OCT-2009 11:44:34 +000 00:00:02        SUCCEEDED           0

复制代码

上完1Z0-053 第17章 Scheduler  (8/40)




回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 19:13 , Processed in 0.043576 second(s), 27 queries .

快速回复 返回顶部 返回列表