The SET_ATTRIBUTE procedure modifies the attributes of a Scheduler object.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL-compatible mode.
Syntax
DBMS_SCHEDULER.SET_ATTRIBUTE (
name IN VARCHAR2,
attribute IN VARCHAR2,
value IN VARCHAR2);
Parameters
Parameter |
Description |
|---|---|
| name | The name of the object. |
| attribute | For more information, see the following Attributes section. |
| value | The new value that is set for the attribute. The value of this parameter cannot be NULL. |
Attributes
Job attributes
The current version of OceanBase Database supports the following job-related attribute values:
Attribute |
Description |
|---|---|
| auto_drop | Valid values: TRUE and FALSE. If you set it to TRUE, the job is automatically dropped after it is completed or automatically disabled. A job is considered completed if one of the following conditions is met:
max_failures. You must call the SET_ATTRIBUTE procedure to set the max_failures attribute. If you set the auto_drop attribute to FALSE, the job is not dropped, and its metadata is retained. You can call the DROP_JOB procedure to explicitly drop the job. By default, the auto_drop attribute is set to TRUE when you create a job.
NoteThis attribute value is not supported in the current version. |
| comments | The comments about the job. This attribute is optional.
NoteThis attribute value is not supported in the current version. |
| credential_name | The name of the credential object that is used for the job. For a local database job, the value must be NULL. For a local external job, if the default value NULL is used, the default credential is selected.
NoteThis attribute value is not supported in the current version. |
| destination_name | The destination database or external destination of the job. This attribute is available only for remote database jobs and remote external jobs. For a job running on a local database or a local external job (an executable file), the value must be NULL.
NoteThis attribute value is not supported in the current version. |
| end_date | The timestamp that consists of the date and time after which the job expires and no longer runs. If the auto_drop attribute is set to TRUE, the job is dropped after the time specified by end_date elapses. If the auto_drop attribute is set to FALSE, the job is disabled, and the status of the job is set to COMPLETED. If you do not set the end_date attribute, the job is repeatedly executed. In this case, if you have set the max_runs or max_failures attribute, the job stops running when either of the values is reached. The time specified in the end_date attribute must be later than that in the start_date attribute. Otherwise, an error is returned. If the values of the end_date and start_date attributes are the same, the job will not be executed, and no error is returned.
NoteThis attribute value is not supported in the current version. |
| job_action | The operation that the job performs. The value depends on the job_type attribute. For example, if the job type is STORED_PROCEDURE, the job_action attribute contains the name of the stored procedure.
NoteThis attribute value is not supported in the current version. |
| job_class | The class to which the job belongs. |
| job_name | The name of the job. The name must be unique in the SQL namespace. If you are creating a job that belongs to another schema, you must qualify it with the schema name. If you do not specify the job name, an error is returned. You can also call the GENERATE_JOB_NAME procedure to use the Scheduler to generate a name, which is a serial number generated in order. You can prefix a string to the number. As a result, the job name is a string with a serial number and can be used in the CREATE_JOB procedure.
NoteThis attribute value is not supported in the current version. |
| job_priority | The priority of the job relative to other jobs in the same class. If multiple jobs in a class are scheduled to execute at the same time, the execution order is determined by the job priority. The attribute value can be an integer from 1 to 5. The value 1 indicates that the job is the first to be executed. If you do not specify the job priority during job creation, the default job priority is 3.
NoteThis attribute value is not supported in the current version. |
| job_type | The type of the job. If you do not specify the job type, an error is returned. You can specify only STORED_PROCEDURE. If you set this parameter to STORED_PROCEDURE, the value of the program_name attribute must be NULL.
NoteThis attribute value is not supported in the current version. |
| max_failures | The number of times the job can fail on consecutive runs before it is automatically disabled. Once a job is disabled, it is no longer executed. The value of the max_failures attribute can be an integer from 1 to 1,000,000. The default value is NULL, which indicates that a new instance is started for the job regardless of the number of previously failed instances. |
| max_runs | The maximum number of times the job can consecutively run. Once the specified maximum number of times is reached, the job is disabled, and its status changes to COMPLETED. The attribute value can be an integer from 1 to 1,000,000. The default value is NULL, which indicates that the execution is repeated forever or until the value of the end_date or max_failures attribute is reached.
NoteThis attribute value is not supported in the current version. |
| number_of_argument | The number of job arguments of the associated program. If you set this attribute, the value of the program_name attribute must be NULL.
NoteThis attribute value is not supported in the current version. |
| program_name | The name of the program that is associated with the job. If the program type is EXECUTABLE, the values of the job_action, job_type, and number_of_arguments attributes must be NULL.
NoteThis attribute value is not supported in the current version. |
| repeat_interval | The procedural language (PL) function or calendar syntax expression that returns the next runtime of the job (including the date and time). If you set this attribute, the value of the schedule_name attribute must be NULL.
NoteThis attribute value is not supported in the current version. |
| start_date | The initial date and time when the job starts or is scheduled to start. If you set this attribute, the value of the schedule_name attribute must be NULL.
NoteThis attribute value is not supported in the current version. |
| instance_id | By default, jobs run in the primary zone. The instance_id attribute specifies the zone where the job runs. If you set instance_id to RANDOM (that is, set the value parameter of the subprogram to RANDOM), the job is randomly dispatched to all zones. |
Program attributes
Attribute |
Description |
|---|---|
| comments | The comments about the program. This attribute is optional. You can add comments to describe the program features or the details about the program usage. |
| number_of_arguments | The number of arguments the program requires. |
| program_type | The type of the program. Only the STORED_PROCEDURE type is supported. |
| program_action | The operation performed by the program. The operation is specified by the program_type attribute. For example, if the program type is STORED_PROCEDURE, the program_action attribute contains the name of the stored procedure. |
Considerations
If you modify an enabled object, the Scheduler disables it, modifies it, and then re-enables it. If an error occurs during enabling, the object is not re-enabled, and an error message is returned. If you modify a disabled object, the object remains disabled after the modification.
For jobs:
If a job has a running instance while calling the
SET_ATTRIBUTEprocedure, the instance is not affected by the call, and the modifications affect only the operations of the job after the call.If a schedule attribute of a job is modified during the running of the job, the next runtime is scheduled based on the new attribute values. The schedule attributes of a job include
schedule_name,start_date,end_date, andrepeat_interval.If a program attribute of a job is modified during job running, the new attribute value takes effect for the next running of the job. The program attributes of a job include
program_name,job_action,job_type, andnumber_of_arguments.If any job parameter values are modified while the job is running, the new values take effect the next time the job runs.
If the schedule of a job is modified during job running, the modification does not affect the running job. The change takes effect only the next time the job runs.
For programs, if you modify the program that a job is currently running, the job continues to run the program using the attribute values before the modification. The next time the job runs, it uses the new attribute values.
For scheduled tasks such as SCHEDULED_TRIGGER_PARTITION_BALANCE (partition balancing task), SCHEDULED_RECYCLE_SCHEMA_HISTORY (schema multi-version history data recycle task), SCHEDULED_RUN_INSPECTION (Root Service self-inspection task), and SCHEDULED_PURGE_RECYCLEBIN (recycle bin purge task), you can set the trigger interval using the repeat_interval attribute of the DBMS_SCHEDULER.SET_ATTRIBUTE subprogram.
Examples:
Change the trigger interval of the scheduled task
SCHEDULED_TRIGGER_PARTITION_BALANCEto 2 days.obclient(sys@oracle001)[SYS]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_TRIGGER_PARTITION_BALANCE', 'repeat_interval', 'FREQ=DAILY; INTERVAL=2');Change the trigger interval of the scheduled task
SCHEDULED_RECYCLE_SCHEMA_HISTORYto 60 minutes.obclient(sys@oracle001)[SYS]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_RECYCLE_SCHEMA_HISTORY', 'repeat_interval', 'FREQ=MINUTELY; INTERVAL=60');Change the trigger interval of the scheduled task
SCHEDULED_RUN_INSPECTIONto 30 minutes.obclient(sys@oracle001)[SYS]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_RUN_INSPECTION', 'repeat_interval', 'FREQ=SECONDLY; INTERVAL=1800');Change the trigger interval of the scheduled task
SCHEDULED_PURGE_RECYCLEBINto 30 minutes.obclient(sys@oracle001)[SYS]> CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_PURGE_RECYCLEBIN', 'repeat_interval', 'FREQ=MINUTELY; INTERVAL=30');