Modifying the Default AWR Tablespace setting May Not Be Stored In WR CONTROL (Doc ID 3075469.1)

Last updated on APRIL 08, 2025

Applies to:

Symptoms

Modifying the default AWR tablespace from SYSAUX to user defined tablespace setting will not take effect and some fixed object partitions will still get created on SYSAUX tablespace .

Although the default behavior is that the modified tablespace setting only applies to partitions created after the setting was changed.

Any existing partitions prior to the setting modification will remain in their former tablespace.

The observation though was that that new partitions were still using SYSAUX instead of using the modified user defined tablespace setting .

Changes

Starting with Oracle Database 19c, the user can specify a user defined tablespace for AWR data/snapshots by using the 'dbms_workload_repository.modify_snapshot_settings' procedure, using 'tablespace_name' parameter.

The following statement can be used to set the user defined tablespace.

SQL> dbms_workload_repository.modify_snapshot_settings(dbid => <DB ID>, tablespace_name=> '<tablespace name>');

This is described in the note - DB 19c: How To Store AWR Snapshots In User Defined Tablespace (Doc ID 2871566.1) 

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.