Storing AWR Snapshots Outside SYSAUX

As you know, AWR snapshots are stored in the SYSAUX tablespace by default, and until version 19c, it was not possible to store them in a separate tablespace. This capability was introduced in version 19c with the addition of the TABLESPACE_NAME parameter to the procedure dbms_workload_repository.modify_snapshot_settings.

Details of the modify_snapshot_settings procedure in 19c:

Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 
SQL> desc dbms_workload_repository.modify_snapshot_settings
Parameter       Type     Mode Default? 
--------------- -------- ---- -------- 
RETENTION       NUMBER   IN   Y        
INTERVAL        NUMBER   IN   Y        
TOPNSQL         NUMBER   IN   Y        
DBID            NUMBER   IN   Y        
TABLESPACE_NAME VARCHAR2 IN   Y        
RETENTION       NUMBER   IN   Y        
INTERVAL        NUMBER   IN   Y        
TOPNSQL         VARCHAR2 IN            
DBID            NUMBER   IN   Y        
TABLESPACE_NAME VARCHAR2 IN   Y    

Viewing the AWR control view in 21c:
In Oracle 21c, the column TABLESPACE_NAME was added to the view awr_pdb_wr_control, which allows us to identify the current tablespace used by AWR snapshots:

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Apr 4 11:55:04 2024
SQL> desc awr_pdb_wr_control
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DBID                                      NOT NULL NUMBER
 SNAP_INTERVAL                             NOT NULL INTERVAL DAY(5) TO SECOND(1)
 RETENTION                                 NOT NULL INTERVAL DAY(5) TO SECOND(1)
 TOPNSQL                                            VARCHAR2(10)
 CON_ID                                             NUMBER
 SRC_DBID                                           NUMBER
 SRC_DBNAME                                         VARCHAR2(128)
 TABLESPACE_NAME                                    VARCHAR2(128)

SQL> select TABLESPACE_NAME from awr_pdb_wr_control;
TABLESPACE_NAME
----------------------
SYSAUX

Creating a new tablespace and setting it for AWR snapshots:

SQL> create tablespace awrtbs;
Tablespace created. 
SQL> exec dbms_workload_repository.modify_snapshot_settings(tablespace_name => 'AWRTBS');
PL/SQL procedure successfully completed.

After creating a snapshot, you will see many segments created in the new tablespace:

SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.

SQL> select count(*) from dba_segments where tablespace_name='AWRTBS';
  COUNT(*)
----------
       118

select segment_name from dba_segments where tablespace_name='AWRTBS';
WRH$_SHARED_POOL_ADVICE
WRH$_SQL_WORKAREA_HISTOGRAM
WRH$_PGA_TARGET_ADVICE
WRH$_INSTANCE_RECOVERY
WRH$_SYSSTAT
WRH$_UNDOSTAT
WRH$_PROCESS_MEMORY_SUMMARY
…