Common parameters Modifying DBMS_SCHEDULER jobs Modifying AutoTask maintenance windows Setting the time zone for Oracle Scheduler jobs Turning off Oracle Scheduler jobs owned by SYS Turning on Oracle Scheduler jobs owned by SYS Modifying the Oracle Scheduler repeat interval for jobs of CALENDAR type Modifying the Oracle Scheduler repeat interval for jobs of NAMED type Turning off autocommit for Oracle Scheduler job creation

Performing common scheduling tasks for Oracle DB instances

Some scheduler jobs owned by SYS can interfere with normal database operations. In such cases, Oracle Support recommends that you modify the schedule. If you need to enable or disable SYS jobs, test the operation on scheduled jobs in a test environment before implementing it in a production environment. To perform tasks for Oracle Scheduler jobs owned by SYS, use the Amazon RDS package rdsadmin.rdsadmin_dbms_scheduler.

The rdsadmin.rdsadmin_dbms_scheduler procedures are supported for the Amazon RDS for Oracle DB engine versions shown in the following table. When using this package, you can specify the SYS jobs listed in the table.

Database release Jobs enabled by default Jobs disabled by default

Oracle Database 19c

Database release	Jobs enabled by default	Jobs disabled by default
Oracle Database 19c	`BSLN_MAINTAIN_STATS_JOB CLEANUP_NON_EXIST_OBJ CLEANUP_ONLINE_IND_BUILD CLEANUP_ONLINE_PMO CLEANUP_TAB_IOT_PMO CLEANUP_TRANSIENT_PKG CLEANUP_TRANSIENT_TYPE DRA_REEVALUATE_OPEN_FAILURES FILE_SIZE_UPD ORA$AUTOTASK_CLEAN PMO_DEFERRED_GIDX_MAINT_JOB PURGE_LOG RSE$CLEAN_RECOVERABLE_SCRIPT SM$CLEAN_AUTO_SPLIT_MERGE`	`FGR$AUTOPURGE_JOB FILE_WATCHER HM_CREATE_OFFLINE_DICTIONARY LOAD_OPATCH_INVENTORY ORA$PREPLUGIN_BACKUP_JOB XMLDB_NFS_CLEANUP_JOB`
Oracle Database 21c	`BSLN_MAINTAIN_STATS_JOB CLEANUP_NON_EXIST_OBJ CLEANUP_ONLINE_IND_BUILD CLEANUP_ONLINE_PMO CLEANUP_TAB_IOT_PMO CLEANUP_TRANSIENT_PKG CLEANUP_TRANSIENT_TYPE DRA_REEVALUATE_OPEN_FAILURES FILE_SIZE_UPD ORA$AUTOTASK_CLEAN PMO_DEFERRED_GIDX_MAINT_JOB PURGE_LOG`	`FGR$AUTOPURGE_JOB FILE_WATCHER HM_CREATE_OFFLINE_DICTIONARY LOAD_OPATCH_INVENTORY ORA$PREPLUGIN_BACKUP_JOB ORA$_ATSK_AUTOSTS XMLDB_NFS_CLEANUP_JOB`


BSLN_MAINTAIN_STATS_JOB
CLEANUP_NON_EXIST_OBJ
CLEANUP_ONLINE_IND_BUILD
CLEANUP_ONLINE_PMO
CLEANUP_TAB_IOT_PMO
CLEANUP_TRANSIENT_PKG
CLEANUP_TRANSIENT_TYPE
DRA_REEVALUATE_OPEN_FAILURES
FILE_SIZE_UPD
ORA$AUTOTASK_CLEAN
PMO_DEFERRED_GIDX_MAINT_JOB
PURGE_LOG
RSE$CLEAN_RECOVERABLE_SCRIPT
SM$CLEAN_AUTO_SPLIT_MERGE


FGR$AUTOPURGE_JOB
FILE_WATCHER
HM_CREATE_OFFLINE_DICTIONARY
LOAD_OPATCH_INVENTORY
ORA$PREPLUGIN_BACKUP_JOB
XMLDB_NFS_CLEANUP_JOB

Oracle Database 21c


BSLN_MAINTAIN_STATS_JOB
CLEANUP_NON_EXIST_OBJ
CLEANUP_ONLINE_IND_BUILD
CLEANUP_ONLINE_PMO
CLEANUP_TAB_IOT_PMO
CLEANUP_TRANSIENT_PKG
CLEANUP_TRANSIENT_TYPE
DRA_REEVALUATE_OPEN_FAILURES
FILE_SIZE_UPD
ORA$AUTOTASK_CLEAN
PMO_DEFERRED_GIDX_MAINT_JOB
PURGE_LOG


FGR$AUTOPURGE_JOB
FILE_WATCHER
HM_CREATE_OFFLINE_DICTIONARY
LOAD_OPATCH_INVENTORY
ORA$PREPLUGIN_BACKUP_JOB
ORA$_ATSK_AUTOSTS
XMLDB_NFS_CLEANUP_JOB

Common parameters for Oracle Scheduler procedures

To perform tasks with Oracle Scheduler, use procedures in the Amazon RDS package rdsadmin.rdsadmin_dbms_scheduler. Several parameters are common to the procedures in the package. The package has the following common parameters.

Parameter name Data type Valid values Default Required Description

Parameter name	Data type	Valid values	Default	Required	Description
`name`	varchar2	The procedures listed in the table in Performing common scheduling tasks for Oracle DB instances	—	Yes	The name of the job to modify.
`attribute`	varchar2	`'REPEAT_INTERVAL'`,`'SCHEDULE_NAME'`	–	Yes	Attribute to modify. To modify the repeat interval for the job, specify `'REPEAT_INTERVAL'`. To modify the schedule name for the job, specify `'SCHEDULE_NAME'`.
`value`	varchar2	A valid schedule interval or schedule name, depending on attribute used.	–	Yes	The new value of the attribute.

name

varchar2

The procedures listed in the table in Performing common scheduling tasks for Oracle DB instances

—

Yes

The name of the job to modify.

attribute

varchar2

'REPEAT_INTERVAL','SCHEDULE_NAME'

–

Yes

Attribute to modify.

To modify the repeat interval for the job, specify 'REPEAT_INTERVAL'.

To modify the schedule name for the job, specify 'SCHEDULE_NAME'.

value

varchar2

A valid schedule interval or schedule name, depending on attribute used.

–

Yes

The new value of the attribute.

Modifying DBMS_SCHEDULER jobs

To modify certain components of Oracle Scheduler, use the Oracle procedure dbms_scheduler.set_attribute. For more information, see DBMS_SCHEDULER and SET_ATTRIBUTE procedure in the Oracle documentation.

When working with Amazon RDS DB instances, prepend the schema name SYS to the object name. The following example sets the resource plan attribute for the Monday window object.


BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name      => 'SYS.MONDAY_WINDOW',
        attribute => 'RESOURCE_PLAN',
        value     => 'resource_plan_1');
END;
/

Modifying AutoTask maintenance windows

Amazon RDS for Oracle instances are created with default settings for maintenance windows. Automated maintenance tasks such as optimizer statistics collection run during these windows. By default, the maintenance windows turn on Oracle Database Resource Manager.

To modify the window, use the DBMS_SCHEDULER package. You might need to modify the maintenance window settings for the following reasons:

You want maintenance jobs to run at a different time, with different settings, or not at all. For example, might want to modify the window duration, or change the repeat time and interval.
You want to avoid the performance impact of enabling Resource Manager during maintenance. For example, if the default maintenance plan is specified, and if the maintenance window opens while the database is under load, you might see wait events such as resmgr:cpu quantum. This wait event is related to Database Resource Manager. You have the following options:
- Ensure that maintenance windows are active during off-peak times for your DB instance.
- Disable the default maintenance plan by setting the resource_plan attribute to an empty string.
- Set the resource_manager_plan parameter to FORCE: in your parameter group. If your instance uses Enterprise Edition, this setting prevents Database Resource Manager plans from activating.

To modify your maintenance window settings

Connect to your database using an Oracle SQL client.

Query the current configuration for a scheduler window.

The following example queries the configuration for MONDAY_WINDOW.


SELECT ENABLED, RESOURCE_PLAN, DURATION, REPEAT_INTERVAL
FROM   DBA_SCHEDULER_WINDOWS 
WHERE  WINDOW_NAME='MONDAY_WINDOW';

The following output shows that the window is using the default values.


ENABLED         RESOURCE_PLAN                  DURATION         REPEAT_INTERVAL
--------------- ------------------------------ ---------------- ------------------------------
TRUE            DEFAULT_MAINTENANCE_PLAN       +000 04:00:00    freq=daily;byday=MON;byhour=22
                                                                ;byminute=0; bysecond=0

Modify the window using the DBMS_SCHEDULER package.

The following example sets the resource plan to null so that the Resource Manager won't run during the maintenance window.


BEGIN
  -- disable the window to make changes
  DBMS_SCHEDULER.DISABLE(name=>'"SYS"."MONDAY_WINDOW"',force=>TRUE);

  -- specify the empty string to use no plan
  DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"', attribute=>'RESOURCE_PLAN', value=>'');

  -- re-enable the window
  DBMS_SCHEDULER.ENABLE(name=>'"SYS"."MONDAY_WINDOW"');
END;
/

The following example sets the maximum duration of the window to 2 hours.


BEGIN
  DBMS_SCHEDULER.DISABLE(name=>'"SYS"."MONDAY_WINDOW"',force=>TRUE);
  DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"', attribute=>'DURATION', value=>'0 2:00:00');
  DBMS_SCHEDULER.ENABLE(name=>'"SYS"."MONDAY_WINDOW"');
END;
/

The following example sets the repeat interval to every Monday at 10 AM.


BEGIN
  DBMS_SCHEDULER.DISABLE(name=>'"SYS"."MONDAY_WINDOW"',force=>TRUE);
  DBMS_SCHEDULER.SET_ATTRIBUTE(name=>'"SYS"."MONDAY_WINDOW"', attribute=>'REPEAT_INTERVAL', value=>'freq=daily;byday=MON;byhour=10;byminute=0;bysecond=0');
  DBMS_SCHEDULER.ENABLE(name=>'"SYS"."MONDAY_WINDOW"');
END;
/

Setting the time zone for Oracle Scheduler jobs

To modify the time zone for Oracle Scheduler, you can use the Oracle procedure dbms_scheduler.set_scheduler_attribute. For more information about the dbms_scheduler package, see DBMS_SCHEDULER and SET_SCHEDULER_ATTRIBUTE in the Oracle documentation.

To modify the current time zone setting

Connect to the database using a client such as SQL Developer. For more information, see Connecting to your DB instance using Oracle SQL developer.

Set the default time zone as following, substituting your time zone for time_zone_name.


BEGIN
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
    attribute => 'default_timezone',
    value => 'time_zone_name'
  );
END;
/

In the following example, you change the time zone to Asia/Shanghai.

Start by querying the current time zone, as shown following.


SELECT VALUE FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE WHERE ATTRIBUTE_NAME='DEFAULT_TIMEZONE';