Introduction
AWR (Automatic Workload Repository) is an Oracle performance monitoring feature that collects, processes, and stores database performance statistics. These statistics are used for problem diagnosis and self-tuning of the database. The collected data can be viewed through database views or generated as HTML or text reports, helping DBAs analyze the current performance state of the database in detail.
Background Processes for AWR
MMON: collects AWR statistics.MMNL: writes ASH and AWR data to disk.
Data Collected by AWR
AWR captures a wide range of performance-related metrics, including:
- Object statistics: access and usage statistics of database segments.
- Time model statistics: data from the
V$SYS_TIME_MODELandV$SESS_TIME_MODELviews. - System and session statistics: selected statistics from the
V$SYSSTATandV$SESSTATviews. - Active Session History (ASH) statistics.
- High-load SQL statements contributing significantly to database workload.
Prerequisites
- Database Edition should be Enterprise.
- Must have an Oracle Diagnostics Pack license.
CONTROL_MANAGEMENT_PACK_ACCESSparameter should be set toDIAGNOSTIC+TUNING.
Reports Provided by AWR
| SQL Script | Description |
|---|---|
awrrpt.sql |
Basic AWR Report |
awrsqrpt.sql |
Standard SQL Statement Report |
awrddrpt.sql |
Period Difference Report on Current Instance |
awrrpti.sql |
Workload Repository Report by Instance (RAC) |
awrgrpt.sql |
AWR Global Report (RAC) |
awrgdrpt.sql |
AWR Global Difference Report (RAC) |
awrinfo.sql |
Script to Output General AWR Information |
Activities in This Guide
Jump to any section directly:
- Report Generation
- Modify AWR Retention & Interval
- Generate a Manual AWR Snapshot
- Modify Tablespace for AWR
- Purge Old Snapshots
Report Generation
Step 1: Generate the Different Types of AWR Reports
Several scripts under $ORACLE_HOME/rdbms/admin generate different flavors of AWR report. This walks through the basic report, a period comparison report, a SQL-specific report, and the general AWR info script.
Location of AWR scripts:
[oracle@orcl ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@orcl admin]$
[oracle@orcl admin]$ ls -lrth awr*
-rw-r--r--. 1 oracle oinstall 744 Jan 25 2017 awrrptinoop.sql
-rw-r--r--. 1 oracle oinstall 1.7K Jan 25 2017 awrrptidc.sql
-rw-r--r--. 1 oracle oinstall 7.5K May 29 2017 awrupd12.sql
-rw-r--r--. 1 oracle oinstall 3.1K May 29 2017 awrsqrpt.sql
-rw-r--r--. 1 oracle oinstall 10K May 29 2017 awrrpti.sql
-rw-r--r--. 1 oracle oinstall 15K May 29 2017 awrinput.sql
-rw-r--r--. 1 oracle oinstall 1.5K May 29 2017 awrgrpt.sql
-rw-r--r--. 1 oracle oinstall 8.5K May 29 2017 awrgrpti.sql
-rw-r--r--. 1 oracle oinstall 13K May 29 2017 awrginp.sql
-rw-r--r--. 1 oracle oinstall 3.6K May 29 2017 awrgdrpt.sql
-rw-r--r--. 1 oracle oinstall 9.2K May 29 2017 awrgdrpi.sql
-rw-r--r--. 1 oracle oinstall 3.8K May 29 2017 awrddrpt.sql
-rw-r--r--. 1 oracle oinstall 1.5K May 29 2017 awrblmig.sql
-rw-r--r--. 1 oracle oinstall 2.9K Jul 29 2017 awrinpnm.sql
-rw-r--r--. 1 oracle oinstall 26K Jul 29 2017 awrgdinp.sql
-rw-r--r--. 1 oracle oinstall 2.5K Jan 4 2018 awrmacro.sql
-rw-r--r--. 1 oracle oinstall 8.2K Aug 9 2018 awrrpt.sql
-rw-r--r--. 1 oracle oinstall 12K Aug 16 2018 awrsqrpi.sql
-rw-r--r--. 1 oracle oinstall 10K Aug 21 2018 awrddrpi.sql
-rw-r--r--. 1 oracle oinstall 14K Jul 14 2024 awrextr.sql
-rw-r--r--. 1 oracle oinstall 6.6K Jul 14 2024 awrload.sql
-rw-r--r--. 1 oracle oinstall 11K Jul 14 2024 awrload_18.sql
-rw-r--r--. 1 oracle oinstall 12K Jul 14 2024 awrextr_pre20.sql
-rw-r--r--. 1 oracle oinstall 31K Jul 14 2024 awrddinp.sql
-rw-r--r--. 1 oracle oinstall 56K Apr 15 2025 awrinfo.sql
[oracle@orcl admin]$
Basic AWR Report:
SQL> @?/rdbms/admin/awrrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
Enter value for report_type:
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new 1: select 'Type Specified: ',lower(nvl('','html')) report_type from dual
Type Specified: html
old 1: select '&&report_type' report_type_def from dual
new 1: select 'html' report_type_def from dual
old 1: select '&&view_loc' view_loc_def from dual
new 1: select 'AWR_PDB' view_loc_def from dual
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------
1718803653 ORCL 1 orcldc CDB$ROOT
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1718803653 1 ORCL orcldc orcl.oraeasy
Using 1718803653 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 9
Listing the last 9 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
orcldc ORCL 179 07 Dec 2025 11:44 1
180 07 Dec 2025 12:30 1
181 07 Dec 2025 13:30 1
182 07 Dec 2025 14:30 1
183 07 Dec 2025 15:30 1
184 07 Dec 2025 16:30 1
185 07 Dec 2025 17:30 1
186 09 Dec 2025 20:09 1
187 09 Dec 2025 21:30 1
188 13 Dec 2025 18:23 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 181
Begin Snapshot Id specified: 181
Enter value for end_snap: 182
End Snapshot Id specified: 182
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_181_182.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrrpt_test
Using the report name awrrpt_test
......
......
End of Report
</body></html>
Report written to awrrpt_test.html
SQL>
AWR Comparasion report:
SQL> @?/rdbms/admin/awrddrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Id DB Name Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
1718803653 1718803653 ORCL 1 1 orcldc
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1718803653 1 ORCL orcldc orcl.oraeasy
Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1718803653 for Database Id for the first pair of snapshots
Using 1 for Instance Number for the first pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 7
Listing the last 7 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
orcldc ORCL 179 07 Dec 2025 11:44 1
180 07 Dec 2025 12:30 1
181 07 Dec 2025 13:30 1
182 07 Dec 2025 14:30 1
183 07 Dec 2025 15:30 1
184 07 Dec 2025 16:30 1
185 07 Dec 2025 17:30 1
186 09 Dec 2025 20:09 1
187 09 Dec 2025 21:30 1
188 13 Dec 2025 18:23 1
Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 180
First Begin Snapshot Id specified: 180
Enter value for end_snap: 181
First End Snapshot Id specified: 181
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1718803653 1 ORCL orcldc orcl.oraeasy
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1718803653 for Database Id for the second pair of snapshots
Using 1 for Instance Number for the second pair of snapshots
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days2: 6
Listing the last 6 days of Completed Snapshots
186 09 Dec 2025 20:09 1
187 09 Dec 2025 21:30 1
188 13 Dec 2025 18:23 1
Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 186
Second Begin Snapshot Id specified: 186
Enter value for end_snap2: 187
Second End Snapshot Id specified: 187
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_180_1_186.html To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrdiff_1_180_1_186.html
......
......
</body></html>
Report written to awrdiff_1_180_1_186.html
SQL>
AWR for specfic SQL ID:
SQL> @?/rdbms/admin/awrsqrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:
Type Specified: html
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1718803653 ORCL 1 orcldc
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 1718803653 1 ORCL orcldc orcl.oraeasy
Using 1718803653 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 7
Listing the last 7 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
orcldc ORCL 179 07 Dec 2025 11:44 1
180 07 Dec 2025 12:30 1
181 07 Dec 2025 13:30 1
182 07 Dec 2025 14:30 1
183 07 Dec 2025 15:30 1
184 07 Dec 2025 16:30 1
185 07 Dec 2025 17:30 1
186 09 Dec 2025 20:09 1
187 09 Dec 2025 21:30 1
188 13 Dec 2025 18:23 1
189 13 Dec 2025 19:30 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 182
Begin Snapshot Id specified: 182
Enter value for end_snap: 183
End Snapshot Id specified: 183
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 22356bkgsdcnh
SQL ID specified: 22356bkgsdcnh
......
......
</body></html>
Report written to sqlawrreport.html
SQL>
Use below script for AWR info:
SQL> @?/rdbms/admin/awrinfo.sql
This script will report general AWR information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrinfo.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrinfo.txt
No errors.
No errors.
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
......
......
Report generation time: +00 00:00:18.8
End of Report
Report written to awrinfo.txt
SQL>
Report excerpt:
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 15 mins snapshot INTERVAL:
| AWR size/day 736.2 MB (7,852 K/snap * 96 snaps/day)
| AWR size/wk 5,153.1 MB (size_per_day * 7) per instance
|
| Estimates based on 13 snaps in past 24 hours:
| AWR size/day 697.8 MB (7,852 K/snap and 13 snaps in past 3.4 hours)
| AWR size/wk 4,884.7 MB (size_per_day * 7) per instance
|
**********************************
(3) Space usage by AWR components (per database)
**********************************
COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED 52.3 52.5 4,121 366.2 2,563.3 44% : 56%
EVENTS 9.5 9.5 748 66.5 465.5 45% : 55%
SQLPLAN 7.0 7.0 551 49.0 343.0 71% : 29%
SPACE 4.8 4.8 374 33.3 232.8 47% : 53%
SQL 3.2 3.2 251 22.3 156.2 51% : 49%
ASH 0.9 0.9 74 6.6 45.9 60% : 40%
SQLTEXT 0.9 0.9 69 6.1 42.9 86% : 14%
SQLBIND 0.9 0.9 69 6.1 42.9 50% : 50%
RAC 0.6 0.6 44 3.9 27.6 44% : 56%
**********************************
The awrinfo.sql excerpt at the end is worth a second look on its own — it estimates AWR's actual disk footprint per day and per week, broken down by component, which is exactly the data you need before deciding on a retention policy in the next section.
Modify AWR Retention & Interval
Step 2: Change the Snapshot Interval and Retention
Check the current interval and retention first, then adjust both using dbms_workload_repository.modify_snapshot_settings. Note that this must be run separately in each container — the CDB root and each PDB maintain independent settings.
Check the current interval & retention:
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control
order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
0 1718803653 +00000 01:00:00.0 +00008 00:00:00.0
3 1593504508 +00000 01:00:00.0 +00008 00:00:00.0
Now change the interval to 15 mins and retention for 15 days (21600 mins):
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 15,
retention => 21600
);
end;
/
PL/SQL procedure successfully completed.
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control
order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
0 1718803653 +00000 00:15:00.0 +00015 00:00:00.0
3 1593504508 +00000 01:00:00.0 +00008 00:00:00.0
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 15,
retention => 21600
);
end;
/
PL/SQL procedure successfully completed.
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control
order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
3 1593504508 +00000 00:15:00.0 +00015 00:00:00.0
Now verify the interval & retention:
SQL> conn /as sysdba
Connected.
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control
order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
0 1718803653 +00000 00:15:00.0 +00015 00:00:00.0
3 1593504508 +00000 00:15:00.0 +00015 00:00:00.0
For the AWR interval, the minimum value is 10 minutes. Retention is also tied to the baseline called MOVING_WINDOW, so retention must always be greater than the MOVING_WINDOW value — the next step walks through what happens when it isn't.
Step 3: Configure the AWR Baseline (Moving Window)
These errors are worth showing deliberately, since they illustrate the two hard constraints Oracle enforces: the interval floor of 10 minutes, and retention always needing to exceed the current moving-window baseline size.
Error while setting interval less than 10 mins:
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 5,
retention => 21600
);
end;
/
begin
*
ERROR at line 1:
ORA-13511: invalid INTERVAL 5, must be in the range (10, 52560000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 213
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 265
ORA-06512: at line 2
Check the current MOVING_WINDOW value & try to set retention below that:
SQL> col BASELINE_NAME for a30
SQL> select baseline_name,
baseline_type,
moving_window_size
from dba_hist_baseline
where baseline_type = 'MOVING_WINDOW';
BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
------------------------------ ------------- ------------------
SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 10,
retention => 5760 -- 4 days
);
end;
/
begin
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (345600)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 213
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 265
ORA-06512: at line 2
Change the MOVING_WINDOW value to 3 & then try to set retention:
SQL> begin
dbms_workload_repository.modify_baseline_window_size(
window_size => 3
);
end;
/
PL/SQL procedure successfully completed.
SQL> select baseline_name,
baseline_type,
moving_window_size
from dba_hist_baseline
where baseline_type = 'MOVING_WINDOW';
BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
------------------------------ ------------- ------------------
SYSTEM_MOVING_WINDOW MOVING_WINDOW 3
SQL>
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 10,
retention => 5760 -- 4 days
);
end;
/
PL/SQL procedure successfully completed.
SQL>
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control
order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
0 1718803653 +00000 00:10:00.0 +00004 00:00:00.0
3 1593504508 +00000 00:15:00.0 +00015 00:00:00.0
SQL>
Now to increase MOVING_WINDOW value, we first need to increase retention:
SQL> begin
dbms_workload_repository.modify_baseline_window_size(
window_size => 8
);
end;
/
begin
*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention (345600)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 1036
ORA-06512: at line 2
SQL> begin
dbms_workload_repository.modify_snapshot_settings(
interval => 15,
retention => 21600
);
end;
/
PL/SQL procedure successfully completed.
SQL> begin
dbms_workload_repository.modify_baseline_window_size(
window_size => 8
);
end;
/
PL/SQL procedure successfully completed.
SQL> select baseline_name,
baseline_type,
moving_window_size
from dba_hist_baseline
where baseline_type = 'MOVING_WINDOW';
BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
------------------------------ ------------- ------------------
SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
SQL>
SQL> select con_id, dbid, snap_interval, retention
from cdb_hist_wr_control order by con_id;
CON_ID DBID SNAP_INTERVAL RETENTION
---------- ---------- -------------------- --------------------
0 1718803653 +00000 00:15:00.0 +00015 00:00:00.0
3 1593504508 +00000 00:15:00.0 +00015 00:00:00.0
SQL>
The sequence here matters: raising MOVING_WINDOW_SIZE back to 8 failed until retention was raised first, because Oracle won't let the baseline window exceed the data it's actually retaining.
Generate a Manual AWR Snapshot
Step 4: Generate a Manual AWR Snapshot
Snapshots normally fire on the configured interval, but dbms_workload_repository.create_snapshot lets you force one immediately, useful right before or after a change you want captured in its own snapshot window.
Check the current time and genearted AWR snaps:
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
---------------------------------------
13-DEC-25 09.06.05.566286 PM +05:30
SQL> col BEGIN_INTERVAL_TIME for a30
SQL> col END_INTERVAL_TIME for a30
SQL> select snap_id, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by snap_id desc;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ------------------------------ ------------------------------
193 13-DEC-25 08.50.41.053 PM 13-DEC-25 09.00.42.594 PM
192 13-DEC-25 08.42.37.081 PM 13-DEC-25 08.50.41.053 PM
191 13-DEC-25 08.28.35.786 PM 13-DEC-25 08.42.37.081 PM
190 13-DEC-25 07.30.15.521 PM 13-DEC-25 08.28.35.786 PM
189 13-DEC-25 06.23.04.246 PM 13-DEC-25 07.30.15.521 PM
188 13-DEC-25 06.12.35.000 PM 13-DEC-25 06.23.04.246 PM
187 09-DEC-25 08.09.44.062 PM 09-DEC-25 09.30.41.431 PM
186 09-DEC-25 07.59.30.000 PM 09-DEC-25 08.09.44.062 PM
185 07-DEC-25 04.30.39.843 PM 07-DEC-25 05.30.49.750 PM
184 07-DEC-25 03.30.30.767 PM 07-DEC-25 04.30.39.843 PM
183 07-DEC-25 02.30.24.354 PM 07-DEC-25 03.30.30.767 PM
182 07-DEC-25 01.30.19.229 PM 07-DEC-25 02.30.24.354 PM
181 07-DEC-25 12.30.08.525 PM 07-DEC-25 01.30.19.229 PM
180 07-DEC-25 11.44.46.012 AM 07-DEC-25 12.30.08.525 PM
179 07-DEC-25 11.33.55.000 AM 07-DEC-25 11.44.46.012 AM
15 rows selected.
SQL>
Create a manual snap:
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
Check the created snap:
SQL> select snap_id, begin_interval_time, end_interval_time
from dba_hist_snapshot
order by snap_id desc;
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ------------------------------ ------------------------------
194 13-DEC-25 09.00.42.594 PM 13-DEC-25 09.07.55.166 PM
193 13-DEC-25 08.50.41.053 PM 13-DEC-25 09.00.42.594 PM
192 13-DEC-25 08.42.37.081 PM 13-DEC-25 08.50.41.053 PM
191 13-DEC-25 08.28.35.786 PM 13-DEC-25 08.42.37.081 PM
190 13-DEC-25 07.30.15.521 PM 13-DEC-25 08.28.35.786 PM
189 13-DEC-25 06.23.04.246 PM 13-DEC-25 07.30.15.521 PM
188 13-DEC-25 06.12.35.000 PM 13-DEC-25 06.23.04.246 PM
187 09-DEC-25 08.09.44.062 PM 09-DEC-25 09.30.41.431 PM
186 09-DEC-25 07.59.30.000 PM 09-DEC-25 08.09.44.062 PM
185 07-DEC-25 04.30.39.843 PM 07-DEC-25 05.30.49.750 PM
184 07-DEC-25 03.30.30.767 PM 07-DEC-25 04.30.39.843 PM
183 07-DEC-25 02.30.24.354 PM 07-DEC-25 03.30.30.767 PM
182 07-DEC-25 01.30.19.229 PM 07-DEC-25 02.30.24.354 PM
181 07-DEC-25 12.30.08.525 PM 07-DEC-25 01.30.19.229 PM
180 07-DEC-25 11.44.46.012 AM 07-DEC-25 12.30.08.525 PM
179 07-DEC-25 11.33.55.000 AM 07-DEC-25 11.44.46.012 AM
16 rows selected.
SQL>
The row count moving from 15 to 16, with new snapshot 194 appearing at the top, confirms the manual snapshot was captured successfully.
Modify Tablespace for AWR
Step 5: Move AWR Data to a New Tablespace
AWR data lives in SYSAUX by default. Moving it to a dedicated tablespace keeps AWR's growth from crowding other SYSAUX-resident components, and makes AWR's actual disk usage easier to monitor on its own. This has to be done separately for the CDB and each PDB.
Checck current tablespace for AWR:
SQL> select dbid,CON_ID,TABLESPACE_NAME from dba_hist_wr_control;
DBID CON_ID TABLESPACE_NAME
---------- ---------- ----------------------------------------------
1718803653 0 SYSAUX
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> select dbid,CON_ID,TABLESPACE_NAME from dba_hist_wr_control;
DBID CON_ID TABLESPACE_NAME
---------- ---------- ----------------------------------------------
1718803653 0 SYSAUX
1593504508 3 SYSAUX
Create a new tablespace for AWR in CDB:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL>
SQL> create tablespace AWR datafile '/u01/app/oracle/oradata/ORCL/awr01.dbf' size 500m autoextend on;
Tablespace created.
SQL>
Set new tablespace for AWR in CDB:
SQL> begin
dbms_workload_repository.modify_snapshot_settings( tablespace_name=> 'AWR');
end;
/
PL/SQL procedure successfully completed.
SQL> select dbid,CON_ID,TABLESPACE_NAME from dba_hist_wr_control;
DBID CON_ID TABLESPACE_NAME
---------- ---------- -----------------------
1718803653 0 AWR
Create tablespace for PDB:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB READ WRITE NO
SQL>
SQL> create tablespace AWRPDB datafile '/u01/app/oracle/oradata/ORCL/orclpdb/awrpdb01.dbf' size 500m autoextend on;
Tablespace created.
Set new tablespace for AWR in PDB:
SQL> begin
dbms_workload_repository.modify_snapshot_settings( tablespace_name=> 'AWRPDB');
end;
/
PL/SQL procedure successfully completed.
SQL> select dbid,CON_ID,TABLESPACE_NAME from dba_hist_wr_control;
DBID CON_ID TABLESPACE_NAME
---------- ---------- -----------------
1718803653 0 AWR
1593504508 3 AWRPDB
SQL>
The final query confirms both containers now report their dedicated tablespaces — AWR for the CDB root, AWRPDB for the PDB — in place of the shared SYSAUX.
Purge Old Snapshots
Step 6: Purge a Range of AWR Snapshots
AWR snapshots normally purge automatically based on the configured retention, but dbms_workload_repository.drop_snapshot_range lets you manually purge a specific range, useful for reclaiming space immediately rather than waiting for the retention window to catch up.
Check current utilization of AWR snaps:
SQL> select tablespace_name,round(sum(bytes)/1024/1024,2) awr_used_mb from dba_segments where owner = 'SYS' and segment_name like 'WR%' group by tablespace_name;
TABLESPACE_NAME AWR_USED_MB
------------------------------ -----------
SYSTEM 3.31
SYSAUX 128.56
AWR 2.69
SQL> select count(*) from dba_hist_snapshot;
COUNT(*)
----------
34
SQL> select min(snap_id), max(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
179 213
SQL>
Drop set of AWR snaps:
SQL> begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 179,
high_snap_id => 200
);
end;
/
PL/SQL procedure successfully completed.
SQL> select tablespace_name,round(sum(bytes)/1024/1024,2) awr_used_mb from dba_segments where owner = 'SYS' and segment_name like 'WR%' group by tablespace_name;
TABLESPACE_NAME AWR_USED_MB
------------------------------ -----------
SYSTEM 3.31
SYSAUX 116.63
AWR 2.69
SQL>
SQL> select min(snap_id), max(snap_id) from dba_hist_snapshot;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
201 213
The SYSAUX figure dropping from 128.56 MB to 116.63 MB, along with the minimum snap ID moving from 179 to 201, confirms the purge freed space by removing exactly the requested range of old snapshots.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment