目录
相关链接
- 【官】129-57 SYS.DBMS_SCHEDULER.set_attribute
- 1. Excel目录
- 2. Oracle SYS.DBMS_SCHEDULER 目录
- 3. 129-41 SYS.DBMS_SCHEDULER.disable
- 4. 129-41 SYS.DBMS_SCHEDULER.enable
- 5. 129-41 SYS.DBMS_SCHEDULER.set_attribute
一、Summary of SET_ATTRIBUTE Procedure
Changes an attribute of a job, schedule, or other Scheduler object
启用程序、作业、链、窗口、数据库路径、外部路径、文件监视器或组。
二、SET_ATTRIBUTE Procedure
This procedure modifies an attribute of a Scheduler object. It is overloaded to accept values of various types. To set an attribute to NULL, use the SET_ATTRIBUTE_NULL procedure. The attributes that can be set depend on the object being altered. All object attributes can be changed, except the object name.
This procedure enables a program, job, chain, window, database destination, external destination, file watcher, or group. When an object is enabled, its enabled attribute is set to TRUE. By default, jobs, chains, and programs are created disabled and database destinations, external destinations, file watchers, windows, and groups are created enabled.
此过程(dbms_scheduler.enable) 可启用程序(program)，作业(job)，链(chain job)，时间窗口(window)，数据库路径(database destination)，外部路径(external destination)，文件监视器( file watcher)。当一个对象的enabled属性被设置为TRUE，默认情况下，创建的作业、链和程序是禁用的，而创建数据库路径、外部路径、文件监视器、窗口和组是启用的。
If a job was disabled and you enable it, the Scheduler begins to automatically run the job according to its schedule. Enabling a disabled job also resets the job RUN_COUNT, FAILURE_COUNT and RETRY_COUNT columns in the *_SCHEDULER_JOBS data dictionary views.
如果某个作业被禁用，启用后调度器将根据该作业的计划自动运行。启用一个被禁用的作业还会重置*_SCHEDULER_JOBS数据字典视图中的作业RUN_COUNT、FAILURE_COUNT和RETRY_COUNT列。
Validity checks are performed before enabling an object. If the check fails, the object is not enabled, and an appropriate error is returned. This procedure does not return an error if the object was already enabled.
在启用对象之前执行有效性检查。如果检查失败，则启用失败，并返回错误信息。如果对象已经启用，此过程不会返回错误。
三、ENABLE
3.1 Syntax 语法
参数有default值：可以不传参，使用默认值
参数无default值：必须传参才可调用
DBMS_SCHEDULER.SET_ATTRIBUTE (
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN {BOOLEAN|DATE|TIMESTAMP|
TIMESTAMP WITH TIME ZONE|TIMESTAMP WITH LOCAL TIME ZONE|
INTERVAL DAY TO SECOND});
DBMS_SCHEDULER.SET_ATTRIBUTE (
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN VARCHAR2,
value2 IN VARCHAR2 DEFAULT NULL);
3.2 执行存储过程
1.如果是命令窗口就用exec 存储过程名，举个例子：
EXEC procedure;--procedure是存储过程名
2.如果是PL/SQL窗口就用 begin 存储过程名 end; 举个例子：
begin
procedure;--procedure是存储过程名
end;
3.如果是程序中调用就用 call 存储过程名 ，举个例子：
hibernateDao.excuteSqlUpdate("{Call proc_stuInfo()}");//存储过程proc_stuInfo
- plsql测试语句
declare
-- Boolean parameters are translated from/to integers:
-- 0/1/null <--> false/true/null
value boolean := sys.diutil.int_to_bool(:value);
begin
-- Call the procedure
sys.dbms_scheduler.set_attribute(name => :name,
attribute => :attribute,
value => value,
value2 => value2);
end;
begin
-- Call the procedure
sys.dbms_scheduler.set_attribute(name => :name,
attribute => :attribute,
value => :value,
value2 => value2);
end;
3.3 Parameters 参数
Table 129-79 SET_ATTRIBUTE Procedure Parameters
| Ser 序号 | Parameter 参数名称 | Type 类型 | Default | IN / OUT | Note 参数说明 | Range 取值范围 |
|---|---|---|---|---|---|---|
| 1 | name | VARCHAR2 | IN | The name of the object 对象的名称。 | [AnyName] | |
| 2 | attribute | VARCHAR2 | IN | See Table 129-81 through Table 129-91. 属性名称，从表129-81到129-91有详细介绍。 Table 129-81 Job Attribute Values --作业属性值 Table 129-82 Event Types Raised by the Scheduler --调度器引发的事件类型 Table 129-83 Program Attribute Values --程序属性值 Table 129-84 Schedule Attribute Values --进度属性值 Table 129-85 File Watcher Attribute Values --文件监视器属性值 Table 129-86 Job Class Attribute Values --作业类属性值 Table 129-87 Window Attribute Values --窗口属性值 Table 129-88 Chain Attribute Values --链属性值 Table 129-89 Database Destination Attribute Values --数据库目标属性值 Table 129-90 External Destination Attribute Values --外部目标属性值 Table 129-91 Group Attribute Values --组属性值 | [AttributeName] | |
| 3 | value | VARCHAR2 | IN | The new value being set for the attribute. This cannot be NULL. To set an attribute value to NULL, use the SET_ATTRIBUTE_NULL procedure.为属性设置的新值。这个不能为空。要将属性值设置为NULL，请使用SET_ATTRIBUTE_NULL存储过程。 | [AttributeValue] | |
| 4 | value2 | VARCHAR2 | IN | The value2 argument is for an optional second value. Most attributes have only one value associated with them, but some can have two.value2参数用于第二个可选值。大多数属性只有一个与之关联的值，但有些属性可以有两个。 | [AttributeValue2] |
Table 129-80 is a directory of Scheduler object types and tables of attributes for the object types.
These object types can be viewed with Scheduler Data Dictionary Views, listed in Oracle Database Administrator’s Guide .
Table 129-80 Attribute Tables for Scheduler Object Types
| Serial 序号 | Scheduler Object Type 调度对象类型 | Table of Attributes 表属性 |
|---|---|---|
| 1 | Job | Table 129-81 Job Attribute Values 作业属性值 |
| 2 | Program | Table 129-83 Program Attribute Values 程序属性值 |
| 3 | Schedule | Table 129-84 Schedule Attribute Values 进度属性值 |
| 4 | File Watcher | Table 129-85 File Watcher Attribute Values 文件监视器属性值 |
| 5 | Job Class | Table 129-86 Job Class Attribute Values 作业类属性值 |
| 6 | Window | Table 129-87 Window Attribute Values 窗口属性值 |
| 7 | Chain | Table 129-88 Chain Attribute Values 链属性值 |
| 8 | Database Destination | Table 129-89 Database Destination Attribute Values 数据库目标属性值 |
| 9 | External Destination | Table 129-90 External Destination Attribute Values 外部目标属性值 |
| 10 | Group | Table 129-91 Group Attribute Values 组属性值 |
| 11 | Credential | Table 129-92 Credential Attribute Values 证书属性值 |
3.4 Usage Notes 使用方式
If an object is altered and it was in the enabled state, the Scheduler first disables it, then makes the change and reenables it. If any errors are encountered during the enable process, the object is not reenabled and an error is generated.
如果一个对象被更改，并且它处于启用状态，set_attribute程序首先禁用它，然后进行更改并重新启用它。如果在启用过程中遇到任何错误，则不会重新启用该对象。
If an object is altered and it was in the disabled state, it remains disabled after it is altered.
如果一个对象被改变，并且它处于禁用状态，那么在它被改变之后它仍然是禁用的。
To run SET_ATTRIBUTE for a window, a group of type WINDOW, or job class, you must have the MANAGE SCHEDULER privilege. Otherwise, you must be the owner of the object being altered or have ALTER privileges on that object or have the CREATE ANY JOB privilege.
要为窗口、window类型的一组或作业类运行SET_ATTRIBUTE，需要以下满足以下至少一种条件：
1、MANAGE SCHEDULER权限。
2、CREATE ANY JOB权限。
3、被修改对象的所有者。
4、对该对象具有ALTER特权，
3.5 Job 作业
If there is a running instance of the job when the SET_ATTRIBUTE call is made, it is not affected by the call. The change is only affects future runs of the job.
如果执行SET_ATTRIBUTE时，要设置属性的作业正在运行，本次运行不受影响，按照修改前属性运行。
If any of the schedule attributes of a job are altered while the job is running, the time of the next job run is scheduled using the new schedule attributes. Schedule attributes of a job include schedule_name, start_date, end_date, and repeat_interval.
如果作业在运行时更改了作业的任何调度属性，则下一次调度时间按照新的配置进行。
作业的调度属性包括 schedule_name、start_date、end_date、repeat_interval。
If any of the program attributes of a job are altered while the job is running, the new program attributes take effect the next time the job runs. Program attributes of a job include program_name, job_action, job_type, and number_of_arguments.
如果作业在运行时更改了作业的任何程序属性，新的属性在下一次作业运行时才会生效。
作业的程序属性包括 program_name、job_action、job_type、number_of_arguments。
If any job argument values are altered while the job is running, the new values take effect the next time the job runs.
如果在作业运行时修改了作业参数值，新值将在下次作业运行时生效。
Granting the ALTER privilege on a job lets a user alter all attributes of that job except its program attributes (program_name, job_type, job_action, program_action, and number_of_arguments) and does not allow a user to use a PL/SQL expression to specify the schedule for a job.
拥有作业ALTER权限的用户即可修改作业的所有属性，
除了它的程序属性(program_name、job_type、job_action、program_action、number_of_arguments)，并且不允许用户使用PL/SQL表达式来指定作业的调度。
Oracle recommends that you not alter a job that was automatically created for you by the database. Jobs that were created by the database have the column SYSTEM set to TRUE in job views.
Oracle建议不要修改数据库自动创建的作业。数据库创建的作业在作业视图中将列SYSTEM设置为TRUE。
3.6 Program 程序
If any currently running jobs use the program that was altered, they continue to run with the program definition prior to the alter. The job runs with the new program definition the next time the job executes.
如果当前运行的作业使用的程序被修改了，此次按照修改前的程序运行作业。下次按照修改后的程序运行作业。
3.7 Schedule 调度
If a schedule is altered, the change does not affect running jobs and open windows that use this schedule. The change only goes into effect the next time the jobs runs or the window opens.
如果更改了调度，此次更改不会影响正在运行的作业和打开使用该调度的窗口。仅在作业下一次运行或窗口打开时生效。
3.8 File Watcher 文件监视器
If a file watcher is altered, any currently running event-based jobs started by the file arrival event are not affected. On the local system, the new file watcher attributes take effect the next time that the file watcher checks for the arrival of the file (every ten minutes by default). On remote systems, there may be an additional delay before the new file watcher attributes take effect.
如果修改了文件监视器，则当前运行的（由文件监视器启动）"event-based"的作业不会受到影响。在本地系统，新的文件监视器属性将在文件监视器下一次检查文件的时生效(默认情况下每10分钟一次)。在远程系统，生效时间可能会有额外的延迟。
3.9 Job Class 作业类
With the exception of the default job class, all job classes can be altered. To alter a job class, you must have the MANAGE SCHEDULER privilege.
除了默认的作业类1外，所有作业类都可以更改。要更改作业类，需要 manag scheduler 权限。
When a job class is altered, running jobs that belong to the class are not affected. The change only takes effect for jobs that have not started running yet.
更改作业类时，正在运行的该类中的作业不受影响。更改后开始运行的作业才生效。
3.10 Window
When a window is altered, it does not affect an active window. The changes only take effect the next time the window opens.
当一个正在执行的窗口被修改，该窗口不受影响。仅在下次及之后窗口状态才会改变。
If there is no current resource plan, when a window with a designated resource plan opens, the Resource Manager activates with that plan.
如果当前没有资源计划，则当带有指定资源计划的窗口打开时，资源管理器将使用该计划激活。
20/12/9
M
作业类：作业类中定义了Resource Consumer Group ，Service(RAC)，日志等级，日志保留时间。要注意的一点是在Resource Manager中，service 参数会将会话映射到一个 资源用户组，如果同时指定了RAC节点和用户组，用户组参数优先 ↩︎
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
