8.8. Managing Attributes Throughout this chapter I have illustrated how you can assign properties for various scheduling components at the time you create themfor example, with the CREATE_JOB or CREATE_WINDOW commands. Sometimes, however, you may have to change the properties of a scheduling component after it has been created. For example, you may want to change the repeat interval of a job that has already been created in the database. One approach to changing properties is to drop and then re-create the object, but this is not always an option. Properties such as repeat intervals, schedule names, logging level, and so on are known as the attributes of a job, program, or schedule object. The DBMS_SCHEDULER package provides the SET_ATTRIBUTE procedure , which allows you to change the attributes of an existing job, job class, program, schedule, window, or window group. For example, suppose that I want to change the comments for the job PURGE_LOG to "This job purges the log entries of jobs and windows". I could issue the following: BEGIN DBMS_SCHEDULER.set_attribute ('PURGE_LOG', 'comments', 'This job purges the log entries of jobs and windows' ); END;
This procedure accepts three parameters:
component_name Name of the component whose property is to be changed. Because jobs, windows, programs, job classes, window groups, and schedules all have unique names, there is no need to specify the type of component here.
attribute Property to be changed as a VARCHAR2 datatype. Valid values will be different for each type of componentfor example, "max_failures" is a valid attribute for jobs but not for programs.
value Value of the attribute. The datatype of this parameter depends on the particular attribute. For example, for windows, the attribute window_priority accepts a VARCHAR2 value ("LOW" or "HIGH"), but the attribute duration expects an INTERVAL datatype .
With this structure in mind, let's explore the valid values for the different types of attributes. Because they are different for each component, I'll examine one component at a time. Because attributes are set explicitly, one at a time, attributes have no default values. (Earlier, I described the default values of parameters when objects are created.) 8.8.1. Jobs Attribute | Datatype | Description |
|---|
auto_drop | BOOLEAN | Whether the job should be dropped after having completed. Valid values are TRUE and FALSE. | comments | VARCHAR2 | Comments. | end_date | TIMESTAMP | Date after which the job will no longer run. The job will be dropped if auto_drop is set or disabled, and the state is changed to COMPLETED if it is. If this attribute is set, schedule_name must be NULL. | instance_stickiness | BOOLEAN | If this is a RAC database, then the job starts on any one of the instances. The next run may be on a different instance, where the load is light. If this attribute is set to TRUE, the same instance is used, even if that is not the one with the lightest load. Valid values are TRUE and FALSE. | job_action | VARCHAR2 | Nature of the program: For a PL/SQL block, specify the anonymous block to execute. For a stored procedure, specify the name of the stored procedure. You can also qualify it with a schema and package name. For an executable, specify the full path name for the operating system executable or shell script.
| job_class | VARCHAR2 | Job class with which this job is associated. | job_priority | PLS_INTEGER | Priority of this job relative to other jobs in the same job class. If multiple jobs within a class are scheduled to be executed at the same time, the job priority determines the order in which jobs from that class are picked up for execution by the job coordinator. Valid values are between 1 (highest priority) and 5 (lowest priority). Default is 3. | job_type | VARCHAR2 | Type of this job. Valid values are: PLSQL_BLOCK STORED_PROCEDURE EXECUTABLE
If this is set, program_name must be NULL. | job_weight | PLS_INTEGER | Degree of parallelism for the job. Valid values are between 1 and 100. Default is 1. | logging_level | PLS_INTEGER | How much information is logged for the job. Overrides the job class properties. Valid values are: DBMS_SCHEDULER.LOGGING_OFF DBMS_SCHEDULER.LOGGING_RUNS (default) DBMS_SCHEDULER.LOGGING_FULL
| max_failures | PLS_INTEGER | How many failures of the job are allowed before the status is set to BROKEN. Must be between 1 and 1,000,000. Default is NULL, which indicates that new instances of the job will be started regardless of how many previous instances have failed. This is also a departure from the old DBMS_JOB package, which only allowed a maximum of 16 failures before marking the job as BROKEN (that number was not configurable). | max_runs | NUMBER | Maximum number of consecutive scheduled runs of the job. Once max_runs is reached, the job is disabled and its state is changed to COMPLETED. Must be between 1 and 1,000,000. Default is NULL, which means that the job will repeat forever or until end_date or max_failures is reached. | number_of_arguments | PLS_INTEGER | Number of arguments if the program is included inline. If this attribute is set, program_name must be NULL. | program_name | VARCHAR2 | Name of a program object to use with this job. If this attribute is set, job_action, job_type, and number_of_arguments must be NULL. | repeat_interval | INTERVAL | Character expression using the calendar string syntax (see the earlier description). For example: "FREQ=YEARLY; BYMONTH=12" | restartable | BOOLEAN | Specifies whether the job should be retried after failing; valid values are TRUE and FALSE. Default is TRUE. | schedule_limit | PLS_INTEGER | In heavily loaded systems, jobs are not always started at their scheduled time. This attribute tells the Scheduler not to start a job at all if the delay in starting the job is longer than the interval specified. Valid values are between 1 minute and 99 days. For example, if a job was supposed to start at noon and the schedule limit is set to 60 minutes, the job will not be run if it has not started to run by 1:00 P.M. If schedule_limit is not specified, the job is executed at some later date as soon as there are resources available to run it. By default, this attribute is set to NULL, which indicates that the job can be run at any time after its scheduled time. A scheduled job run that is skipped because of this attribute does not count against the number of runs and failures of the job. An entry in the job log will be made to reflect the skipped run. | schedule_name | VARCHAR2 | Name of a schedule, window, or window group to use as the schedule for this job. If this attribute is set, end_date, start_date, and repeat_interval must all be NULL. | start_date | VARCHAR2 | Original timestamp at which this job started or will be scheduled to start. If this attribute is set, schedule_name must be NULL. | stop_on_window_close | BOOLEAN | Window close choice. When a job is assigned a window and the window closes while the job is running, what should happen? If this attribute is set to TRUE, the job will stop as well. If FALSE, the job runs to completion even if the window closes. |
8.8.2. Job Classes Attribute name | Datatype | Description |
|---|
comments | VARCHAR2 | Comments. | log_history | PLS_INTEGER | Amount of history (in days) to keep in the logs for this job class. The range of valid values is 1 through 999. | logging_level | PLS_INTEGER | Specifies how much information is logged; valid values are: DBMS_SCHEDULER.LOGGING_OFF DBMS_SCHEDULER.LOGGING_RUNS (default) DBMS_SCHEDULER.LOGGING_FULL
| resource_consumer_group | VARCHAR2 | Resource consumer group with which this job class should be associated. If this attribute is set, service must be NULL. | service | VARCHAR2 | Service name defined in the database to which the job class belongs. Default is NULL, which implies the default service. If this attribute is set, resource_consumer_group must be NULL. |
8.8.3. Schedules Attribute name | Datatype | Description |
|---|
comments | VARCHAR2 | Comments. | end_date | TIMESTAMP | Cutoff timestamp after which the schedule will not specify any dates. | repeat_interval | VARCHAR2 | Character expression using the calendar string syntax (see the earlier description). For example: "FREQ=YEARLY; BYMONTH=12" | start_date | TIMESTAMP | Starting timestamp used by the calendar syntax. |
8.8.4. Programs Attribute name | Datatype | Description |
|---|
comments | VARCHAR2 | Comments. | number_of_arguments | PLS_INTEGER | Number of arguments of the program. | program_action | VARCHAR2 | Nature of the program: For a PL/SQL block, specify the anonymous block to execute. For a stored procedure, specify the name of the stored procedure. You can also qualify it with a schema and package name. For an executable, specify the full path name for the operating system executable or shell script.
| program_type | VARCHAR2 | Type of program; valid values are: PLSQL_BLOCK STORED_PROCEDURE EXECUTABLE
|
8.8.5. Windows Attribute name | Datatype | Description |
|---|
comments | TIMESTAMP | Comments. | duration | INTERVAL | Duration of the window. | end_date | TIMESTAMP | Timestamp after which the window will no longer open. If this attribute is set, schedule_name must be NULL. | repeat_interval | VARCHAR2 | Character string using the calendar string syntax (see the earlier description). If this attribute is set, schedule_name must be NULL. | resource_plan | VARCHAR2 | Resource plan to be associated with a window. | schedule_name | TIMESTAMP | Name of a schedule to use with this window. If this attribute is set, start_date, end_date, and repeat_interval must all be NULL. | start_date | TIMESTAMP | Next timestamp on which this window is scheduled to open. If this attribute is set, schedule_name must be NULL. | window_priority | VARCHAR2 | Priority of the window. Must be either LOW or HIGH. |
8.8.6. Window Groups Attribute name | Datatype | Description |
|---|
comments | TIMESTAMP | Comments for the Window group. This is the only attribute allowed for a window group. |
|
