In Oracle database there is a component called “Automatic Workload Repository” (AWR). AWR collects, processes, and maintains performance statistics for the database.

Moreover, AWR is taken by default on hourly basis and these snapshots are by default stored in SYSAUX tablespace. One of the reasons why you would like to offload AWR to a different tablepsace is because you might have many components in your database and they are installed in SYSAUX so you would like to have a separate space for it.

In this blog post, I will illustrate how can you store AWR in user-tablespace instead of SYSAUX:

First, I will create a user tablespace and will call it TS_AWR

  SQL> CREATE TABLESPACE TS_AWR DATAFILE

  ‘/opt/oracle/oradata/ORCLCDB/PDB_TEMP/TS_AWR.DBF’ SIZE 20M

  LOGGING ONLINE PERMANENT BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Then, execute the following dbms_workload_repository package to change database snapshot settings, to find DBID for a pluggable database [select dbid from v$pdbs]:

SQL> exec dbms_workload_repository.modify_snapshot_settings(dbid =>3048656388, tablespace_name=> ‘TS_AWR’);

PL/SQL procedure successfully completed.

Now, to Test it….manually execute a snapshot:

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Checking the new tablespace TS_AWR…clearly AWR tables and objects were created successfully: