DBMS

it2022-05-05 126

参考《Oracle® Database Database PL/SQL Packages and Types Reference》

DBMS_SCHEDULER提供了一系列PL/SQL程序可以调用的存储过程和高度功能。

1，DBMS_SCHEDULER弃用了程序

oracle建议在新的应用程序中不要使用弃用了的子程序。如下两个子程序从12.1版本开始弃用：

CREATE_CREDENTIAL Procedure DROP_CREDENTIAL Procedure

2，DBMS_SCHEDULER权限模式

DBMS_SCHEDULER包忽略通过角色对调度程序对象(例如作业或链)授予的特权。对象特权必须直接授予用户。

3，DBMS_SCHEDULER规则和限制

1）只有SYS用户可以对SYS模式中的对象执行操作。

2）有些存储过程在接受以逗号分隔的对象名称列表时，如果在某个对象名处执行返回了错误，存储过程就会停止执行该列表，后面对象的任务也就都不会执行。比如下面这个语句，如果执行到job3时发现无法停止job3，那么后面的jobclass1，jobclass2，jobclass3这三个JOB都不会执行停止操作，而job1和job2是正常执行停止操作了的。

DBMS_SCHEDULER.STOP_JOB ('job1, job2, job3,sys.jobclass1, sys.jobclass2, sys.jobclass3');

3）对不存在的对象执行操作将返回一个PL/SQL异常，说明该对象不存在。

4，DBMS_SCHEDULER操作说明

repeat_interval字段的值：

名称描述FREQ循环周期类型。其值为 YEARLY, MONTHLY, WEEKLY, DAILY,HOURLY, MINUTELY, SECONDLY.；如果有创建schedule，也可以设定为自定义的周期类型。INTERVAL指定循环周期的频次，默认值为1，最大值为99，比如'FREQ=WEEKLY;INTERVAL=3'就表示每三周执行一次。BYMONTH这指定要在哪个月或哪个月执行作业。可以用2或FEB(月份前三个字符的缩写)代表二月，6或JUL代表六月。BYWEEKNO指定一个数字，代表一年中的第几个星期执行作业。按照ISO-8601标准，星期一是一个星期的第一天。一年中的第一个星期是这个星期既要包含星期四，又要包含1月4号；比如某年的1月1号是星期五，则下个星期才能算是第一个星期。一年一般是52或53个星期，第一个星期和最后一个星期往往都会处在两个年份中；比如1998年的第一个星期的星期一为1997-12-29，1998年的最后一个星期（第53周）的星期日是1999-01-03。BYWEEKNO只能用于YEARLY这个周期类型。例如不能使用 "FREQ=YEARLY; BYWEEKNO=1;BYMONTH=12" 和 "FREQ=YEARLY;BYWEEKNO=53;BYMONTH=1" .这样的语法。BYYEARDAY指定一个数字代表一年中的哪一天。有效值为1-366。例如指定数字69，则它代表平年的3月10日，或者代表闰年的3月9日。另外，-2代表12月30日，这种负数表达可以避免平年闰年的影响。BYDATE指定一个[YYYY]MMDD格式的日期值，代表具体某一天执行作业。例如，BYDATE=0115,0315,0615,0915,1215,20060115。也可以使用SPAN修饰符指定一连串的日期，例如，可以将BYDATE=0110,0111,0112,0113,0114表示成BYDATE=0110+SPAN:5D。SPAN前面的'+'表示从提供的日期作为开始的连续日期，'-'表示从提供的日期作为结尾的连续日期，'^'表示以提供日期作为中心连续的n天或者n个星期，如果n为偶数，则n自动加1变为奇数，这样就可以在提供日期前后补足相同的天数。OFFSET修饰符可以修改提供的日期，对提供的日期增加/减去n天或n周；例如，BYDATE=0205-OFFSET:2W相当于BYDATE=0205-14D(OFFSET可以省略)，也就是2月5号往前减2周的日期，也就是BYDATE=0122。BYMONTHDAY它将月份的日期指定为一个数字。有效值为1到31。例如10，表示所选月份的第10天。也可以用负号(-)从最后一天开始倒数，例如，BYMONTHDAY=-1表示该月的最后一天。BYDAY以 MON,TUE...这样的形式指定一个星期里星期一到星期日中的星期几。当使用YEARLY或者MONTHLY时可以在星期前加数字，比如FREQ=YEARLY;BYDAY=12 FRI则表示一年中的第12个星期五；还可以使用减号，比如FREQ=MONTHLY;BYDAY=-1 FRI表示一个月中最后一个星期五。BYHOUR这指定了作业运行的时钟。有效值为0到23。例如，10表示上午10点。BYMINUTE这指定了作业运行的分钟。有效值为0到59。例如，45表示超过所选小时的45分钟。BYSECOND这指定要运行作业的秒钟。有效值为0到59。例如，30表示超过所选分钟的30秒。BYSETPOS当设定完时间参数，根据日历确定了所有的JOB执行时间列表，可以通过设定BYSETPOS来指定这个列表中某个具体位置的时间作为真正的执行时间；比如FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1表示一个月的最后一个工作日执行JOB，首先FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; 可以根据日历确定所有的JOB执行日期列表，再通过BYSETPOS=-1来确定列表中的最后一项作为实际的JOB执行时间。BYSETPOS的取值范围为1-9999，可以用负数表示从后往前数的位置。一般BYSETPOS语句是最后执行的。BYSETPOS只支持MONTHLY和YEARLY这两种周期类型。INCLUDE使用CREATE_SCHEDULE创建的一个或多个调度，来为补充JOB执行时间，如果自定义的调度与日历算法有重复的执行时间，则只取一个值。INCLUDE语句只能按天来调度，不能与BYHOUR , BYMIN , BYSECOND .一起使用。EXCLUDE使用CREATE_SCHEDULE创建的一个或多个调度，来排除一些JOB执行时间。EXCLUDE语句只能按天来调度，不能与BYHOUR , BYMIN , BYSECOND .一起使用。INTERSECT指定日历表达式结果与由一个或多个自定义调度得到的时间戳集之间的交集。例如，两个自定义调度last_sat,end_qtr的结果集为3/31/2005, 6/28/2005, 9/12/2005, 12/31/2005，当使用FREQ=MONTHLY; BYMONTHDAY=-1; INTERSECT=last_sat,end_qtr时得到的结果只有3/31/2005,12/31/2005；其中FREQ=MONTHLY; BYMONTHDAY=-1; 表示每个月的最后一天。INTERSECT语句只能按天来调度，不能与BYHOUR , BYMIN , BYSECOND .一起使用。PERIODS强制定义一个周期内执行的频次，如FREQ=YEARLY;BYDATE=0301,0601,0901,1201;PERIODS=4表示每年要执行四次，并且必须指定四次执行的时间。它可以用来表示一个会计年度中的四个季度。BYPERIOD选择PERIODS定义的频次，如 BYPERIOD=2,4表示选择上例中PERIODS定义第二和第四季度。

5，DBMS_SCHEDULER数据结构

DBMS_SCHEDULER包定义了对象类型和表类型：

OBJECT Types • JOBARG Object Type • JOB_DEFINITION Object Type • JOBATTR Object Type • SCHEDULER$_STEP_TYPE Object Type • SCHEDULER$_EVENT_INFO Object Type • SCHEDULER_FILEWATCHER_RESULT Object Type • SCHEDULER_FILEWATCHER_REQUEST Object Type TABLE Types • JOBARG_ARRAY Table Type • JOB_DEFINITION_ARRAY Table Type • JOBATTR_ARRAY Table Type • SCHEDULER$_STEP_TYPE_LIST Table Type

5.1 DBMS_SCHEDULER JOBARG Object Type

5.2 JOBARG_ARRAY Table Type

5.3 DBMS_SCHEDULER JOB_DEFINITION Object Type

5.4 JOB_DEFINITION_ARRAY Table Type

5.5 JOBATTR Object Type

.5.6 JOBATTR_ARRAY Table Type

5.7 SCHEDULER$_STEP_TYPE Object Type

5.8 SCHEDULER$_STEP_TYPE_LIST Table Type

5.9 SCHEDULER$_EVENT_INFO Object Type

5.10 SCHEDULER_FILEWATCHER_RESULT Object Type

5.11 SCHEDULER_FILEWATCHER_REQUEST Object Type

6，DBMS_SCHEDULER子程序概要

6.1 ADD_EVENT_QUEUE_SUBSCRIBER Procedure

6.2 ADD_GROUP_MEMBER Procedure

6.3 ADD_JOB_EMAIL_NOTIFICATION Procedure

6.4 ADD_TO_INCOMPATIBILITY Procedure

6.5 ALTER_CHAIN Procedure

6.6 ALTER_RUNNING_CHAIN Procedure

6.7 CLOSE_WINDOW Procedure

6.8 COPY_JOB Procedure

6.9 CREATE_CHAIN Procedure

6.10 CREATE_CREDENTIAL Procedure

6.11 CREATE_DATABASE_DESTINATION Procedure

6.12 CREATE_EVENT_SCHEDULE Procedure

6.13 CREATE_FILE_WATCHER Procedure

6.14 CREATE_GROUP Procedure

6.15 CREATE_INCOMPATIBILITY Procedure

6.16 CREATE_JOB Procedure

这个存储过程创建单个JOB，创建JOB的enabled属性必须为TRUE才可以通过调度来执行JOB。如果JOB被disable了，可以通过 SET_ATTRIBUTE 存储过程来开启JOB。

创建JOB有多少不同的语法及相应的功能　：

Syntax Creates a job in a single call without using an existing program or schedule: DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, job_type IN VARCHAR2, job_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, repeat_interval IN VARCHAR2 DEFAULT NULL, end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL, credential_name IN VARCHAR2 DEFAULT NULL, destination_name IN VARCHAR2 DEFAULT NULL); Creates a job using a named schedule object and a named program object: DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, program_name IN VARCHAR2, schedule_name IN VARCHAR2, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL, job_style IN VARCHAR2 DEFAULT 'REGULAR', credential_name IN VARCHAR2 DEFAULT NULL, destination_name IN VARCHAR2 DEFAULT NULL); Creates a job using a named program object and an inlined schedule: DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, program_name IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, repeat_interval IN VARCHAR2 DEFAULT NULL, end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL, job_style IN VARCHAR2 DEFAULT 'REGULAR', credential_name IN VARCHAR2 DEFAULT NULL, destination_name IN VARCHAR2 DEFAULT NULL); Creates a job using a named schedule object and an inlined program: DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, schedule_name IN VARCHAR2, job_type IN VARCHAR2, job_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL, credential_name IN VARCHAR2 DEFAULT NULL, destination_name IN VARCHAR2 DEFAULT NULL); Creates a job using an inlined program and an event: DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, job_type IN VARCHAR2, job_action IN VARCHAR2, number_of_arguments IN PLS_INTEGER DEFAULT 0, start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, event_condition IN VARCHAR2 DEFAULT NULL, queue_spec IN VARCHAR2, end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL, credential_name IN VARCHAR2 DEFAULT NULL, destination_name IN VARCHAR2 DEFAULT NULL); Creates a job using a named program object and an event: DBMS_SCHEDULER.CREATE_JOB ( job_name IN VARCHAR2, program_name IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE, event_condition IN VARCHAR2, queue_spec IN VARCHAR2, end_date IN TIMESTAMP WITH TIME ZONE, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL, job_style IN VARCHAR2 DEFAULT 'REGULAR', credential_name IN VARCHAR2 DEFAULT NULL, destination_name IN VARCHAR2 DEFAULT NULL);

参数说明：

参数描述job_name指定JOB名。在同一个schema中名称必须唯一。如果是在为别的schema创建JOB时需要在名称前指定schema。创建JOB时一定要指定JOB名，也可以使用GENERATE_JOB_NAME存储过程生成JOB名。GENERATE_JOB_NAME生成的是一个数字，可以用这个数字做前缀，再加一个字符串来作为JOB名。job_type

建JOB时必须指定JOB类型。支持的JOB类型有：

- PLSQL_BLOCK

指定作业是一个匿名PL/SQL块。这个类型不支持JOB或程序使用参数。因此arguments参数的值必须为0。

- STORED_PROCEDURE

指定JOB为PL/SQL 或者 Java存储过程或者外部C语言子程序。只支持存储过程，而不支持具有返回值的函数。

- EXECUTABLE

指定作业将使用外部可执行文件在数据库外部运行。外部JOB是可以从操作系统的命令行执行的任何操作。不支持Anydata参数。JOB拥有者需要有CREATE EXTERNAL JOB权限。

- CHAIN

这指定作业是一个链。链不支持参数，因此arguments参数值必须为0。

- EXTERNAL_SCRIPT

指定JOB是一个shell命令执行的外部脚本。windows下是cmd.exe，linux下是sh shell，linux下如果使用其它解释器，可以在脚本的第一行前面加上#!标明解释器。

- SQL_SCRIPT

指定JOB为SQL*Plus脚本。使用时需要指明操作系统用户名和密码，也有可能会需要数据库的连接认证。SQL*Plus脚本由SQL*Plus可执行文件运行。如果使用数据库连接认证，需要使用set_attribute来设定Connect_Credential_Name属性；如果没有连接认证，则必须提供带账号密码的连接语句。JOB拥有者必须有CREATE EXTERNAL JOB系统权限。

- BACKUP_SCRIPT

指定作业是一个RMAN备份脚本。脚本在执行命令之前需要运行一条连接语句，该语句使用密码或OS身份验证。作业指向包含有效操作系统用户名和密码的凭据。RMAN会话在此操作系统用户下运行。调度程序使用当前Oracle home中的RMAN可执行文件运行脚本，如果缺少该脚本，则抛出错误。JOB拥有者必须有CREATE EXTERNAL JOB系统权限。

job_action

指定作业的操作。如果内联程序没有指定job_action，则在创建JOB时会报错。job_action在自治事务中执行，并且所有自治事务准则和限制都适用。例如，在线DDL操作不允许在自治事务中进行，因此也不能在job_action中使用。可以使用如下的action:

- For a PL/SQL block:

执行PL/SQL代码的操作。例如， my_proc(); 或者 BEGIN my_proc(); END; 或者 DECLARE arg pls_integer:= 10; BEGIN my_proc2(arg); END; .注意，调度程序将job_action封装在自己的块中，并将以下内容传递给PL/SQL以执行： DECLARE ... BEGIN job_action END; 这样做是为了声明一些内部调度器变量。除了PL / SQL代码中的event_message之外，可以在属性中包含任何调度程序元数据属性。可以像使用任何其他PL/SQL标识符一样使用属性名，并且调度程序会为它分配一个值。

- For a stored procedure:

action是存储过程的名称。如果存储过程的schema与JOB的schema不同，则必须指定存储过程的schema。模式名和存储过程名需要用双引号括起来。例如,job_action=>'"Schema"."Procedure" '。当作业或程序类型为STORED_PROCEDURE时，不支持带有INOUT或OUT参数的PL/SQL过程作为job_action。

- For an executable:

action是外部可执行文件的名称，包括完整的路径名，但不包括任何命令行参数。如果操作以单个问号('?')开始，则问号将被本地作业的Oracle home目录或远程作业的Scheduler代理home的路径所替代。如果操作包含一个at符号('@')，并且作业是本地的，则用当前Oracle实例的SID替换at符号。注意:不支持Shell脚本语法，只支持可执行文件的名称和路径语法。

- For a chain:

该操作是调度程序链对象的名称。如果chain与JOB的schema不同，则必须指定chain的schema。

- For an external script:

job_action必须是操作系统脚本或内联操作系统脚本的路径。如果job_action是脚本的路径，那么脚本必须驻留在作业运行的每台计算机上。job_action可以直接包含对SQL*Plus或RMAN可执行程序的调用，而无需指定其完整路径，因为它们存储在运行该作业的每台计算机的默认位置。作业只能有字符串参数或可以转换为字符串的参数。当调用脚本时，这些参数是按位置传递的。作业必须指向包含有效操作系统用户名和密码的凭据。

- For a SQL script:

job_action必须是SQL*Plus脚本或内联脚本的路径SQL*Plus脚本。如果job_action是脚本的路径，那么脚本必须驻留在作业运行的每台计算机上。作业只能有字符串参数或可以转换为字符串的参数。当调用脚本时，这些参数是按位置传递的。如果参数被命名，它们也被绑定到SQL*Plus会话中的命名变量。

- For a backup script:

job_action是RMAN脚本或内联RMAN脚本的路径。如果program_action是脚本的路径，那么脚本必须驻留在程序运行的每台计算机上。作业只能有字符串参数或可以转换为字符串的参数。当调用脚本时，这些参数是按位置传递的。

number_of_arguments指定作业期望的参数数量。范围为0-255，默认值为0。program_name与作业关联的程序的名称。如果程序是EXECUTABLE类型的，则作业所有者必须具有CREATE EXTERNAL JOB系统特权。start_date指定此作业计划开始的第一个日期和时间。如果start_date和repeat_interval为空，那么一旦启用该作业，就会调度该作业运行。对于使用日历表达式指定重复间隔的重复作业，start_date用作参考日期。JOB第一次在满足日历表达式的时间执行时，该时间必须是当前时间或者当前时间之后的时间。调度程序不能保证作业在准确的时间执行，因为系统可能超载，因此资源不可用。event_condition这是一个基于事件源队列表列的条件表达式。表达式必须具有高级队列规则的语法。因此，如果消息有效负载是对象类型，并且在表达式中为对象属性加上tab.user_data前缀，则可以在表达式中包含用户数据属性。queue_spec

这个参数指定了以下任一项:

- 启动此特定作业的事件被编入队列的源队列。如果它是安全的，那么queue_spec参数就是queue_name、agent_name的一对值。如果不安全，则只需要提供队列名称。如果没有提供完全限定的队列名称，则假定该队列位于作业所有者的模式中。对于安全队列，提供的代理名称应该属于当前订阅到队列的有效代理。

- 文件监视程序名称。

repeat_interval此属性指定作业重复的频率。可以使用日历或PL/SQL表达式指定重复间隔。对指定的表达式进行计算，以确定下次运行作业的时间。如果未指定重复间隔，则作业在指定的开始日期只运行一次。 schedule_name与此作业关联的计划、窗口或窗口组的名称。 job_class此作业关联的类。end_date此属性指定作业过期后不再运行的日期和时间。在结束日期之后，如果auto-drop为true，则作业将被删除。如果auto-drop为false，则作业将被禁用，并且作业的状态设置为“COMPLETED”。如果未指定end_date的值，作业将永远重复，除非设置了max_runs或max_failures，使其当达到任一值时，作业停止。结束日期的值必须在开始日期的值之后。如果结束日期小于开始日期，则将生成错误。如果结束日期与开始日期相同，则不会执行作业，也不会生成任何错误。 comments此属性指定关于作业的注释。默认情况下，这个属性是 NULL。job_style

正在创建的作业的样式。这个参数可以有以下值之一:

- REGULAR

创造一个固定的JOB。这是默认值。

- LIGHTWEIGHT

创建一个轻量级JOB。仅当JOB引用程序对象时才允许此值。当有许多频繁运行的短期JOB时，请使用轻型作业。在某些情况下，使用轻量级JOB可以获得很小的性能提升。

- IN_MEMORY_RUNTIME

创建内存中的运行时时间JOB。这些作业基于轻型作业结构，因此应用了相同的规则和限制；但是，它们通过保持内存中的缓存进一步提高了性能，从而最小化了运行前和运行后操作的磁盘访问。

- IN_MEMORY_FULL

credential_name destination_name enabled auto_drop

专利

最新回复(0)