Script:以下脚本可以用于诊断SYSAUX表空间使用情况
./opatch lsinventory -detail@?/rdbms/admin/awrinfoselect dbms_stats.get_stats_history_retention from dual;select dbms_stats.get_stats_history_availability from dual;select min(SAVTIME), max(SAVTIME) from WRI$_OPTSTAT_TAB_HISTORY;select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_ind_history;select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histhead_history;select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_histgrm_history;select min(SAVTIME), max(SAVTIME) from sys.wri$_optstat_aux_history;select count(*) from sys.wri$_optstat_tab_history;select count(*) from sys.wri$_optstat_ind_history;select count(*) from sys.wri$_optstat_histhead_history;select count(*) from sys.wri$_optstat_histgrm_history;select count(*) from sys.wri$_optstat_aux_history;select count(*) from sys.wri$_optstat_opr;
示例输出： ~~~~~~~~~~~~~~~AWR INFO Report~~~~~~~~~~~~~~~Report generated at08:57:12 on Jun 01, 2012 ( Friday ) in Timezone -04:00select count(*) from sys.wri$_optstat_aux_history;select count(*) from sys.wri$_optstat_opr;Warning: Non Default AWR Setting!--------------------------------------------------------------------------------Snapshot interval is 60 minutes and Retention is 8 days DB_ID DB_NAME HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR------------ --------- ---------------------------------------- ----- ----------------- ------------ ---* 195600696 PROD maclean1.oracle.com - Linux x86 64-bit 1 07:42:19 (06/01) 65130 YES 195600696 PROD maclean2.oracle.com - Linux x86 64-bit 2 07:41:20 (06/01) 65226 YES########################################################(I) AWR Snapshots Information########################################################*****************************************************(1a) SYSAUX usage - Schema breakdown (dba_segments)*****************************************************|| Total SYSAUX size 193.6 MB ( 1% of 32,768.0 MB MAX with AUTOEXTEND ON )|| Schema SYS occupies 101.1 MB ( 52.2% )| Schema SYSMAN occupies 74.5 MB ( 38.5% )| Schema SYSTEM occupies 13.7 MB ( 7.1% )| Schema WMSYS occupies 3.5 MB ( 1.8% )| Schema DBSNMP occupies 0.8 MB ( 0.4% )|********************************************************(1b) SYSAUX occupants space usage (v$sysaux_occupants)********************************************************|| Occupant Name Schema Name Space Usage| -------------------- -------------------- ----------------| EM SYSMAN 74.5 MB| SM/AWR SYS 56.2 MB| LOGMNR SYSTEM 12.3 MB| SM/ADVISOR SYS 8.9 MB| SM/OPTSTAT SYS 7.7 MB| SM/OTHER SYS 6.9 MB| WM WMSYS 3.5 MB| SQL_MANAGEMENT_BASE SYS 1.7 MB| PL/SCOPE SYS 1.6 MB| AO SYS 1.5 MB| XSOQHIST SYS 1.5 MB| LOGSTDBY SYSTEM 1.4 MB| STREAMS SYS 1.0 MB| EM_MONITORING_USER DBSNMP 0.8 MB| JOB_SCHEDULER SYS 0.5 MB| SMON_SCN_TIME SYS 0.5 MB| AUTO_TASK SYS 0.3 MB| AUDIT_TABLES SYS 0.0 MB| EXPRESSION_FILTER EXFSYS 0.0 MB| ORDIM ORDSYS 0.0 MB| ORDIM/ORDDATA ORDDATA 0.0 MB| ORDIM/ORDPLUGINS ORDPLUGINS 0.0 MB| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA 0.0 MB| SDO MDSYS 0.0 MB| STATSPACK PERFSTAT 0.0 MB| TEXT CTXSYS 0.0 MB| TSM TSMSYS 0.0 MB| ULTRASEARCH WKSYS 0.0 MB| ULTRASEARCH_DEMO_USE WK_TEST 0.0 MB| XDB XDB 0.0 MB| XSAMD OLAPSYS 0.0 MB|| Others (Unaccounted space) 12.9 MB|******************************************(1c) SYSAUX usage - Unregistered Schemas******************************************| This section displays schemas that are not registered| in V$SYSAUX_OCCUPANTS||| Total space 0.0 MB|*************************************************************(1d) SYSAUX usage - Unaccounted space in registered schemas*************************************************************|| This section displays unaccounted space in the registered| schemas of V$SYSAUX_OCCUPANTS.|| Unaccounted space in SYS/SYSTEM 12.9 MB|| Total space 12.9 MB|*************************************(2) Size estimates for AWR snapshots*************************************|| Estimates based on 60 mins snapshot INTERVAL:| AWR size/day 674.3 MB (28,768 K/snap * 24 snaps/day)| AWR size/wk 4,719.8 MB (size_per_day * 7) per instance| AWR size/wk 9,439.5 MB (size_per_day * 7) per database|| Estimates based on 1 snaps in past 24 hours:| AWR size/day 533.8 MB (28,768 K/snap and 1 snaps in past 1.3 hours)| AWR size/wk 3,736.5 MB (size_per_day * 7) per instance| AWR size/wk 7,472.9 MB (size_per_day * 7) per database|**********************************(3a) Space usage by AWR components (per database)**********************************COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%--------- --------- ------ ------------ ---------- ----------- ----------------FIXED 35.8 63.7 18,336 340.2 2,381.5 56% : 44%EVENTS 5.8 10.3 2,976 55.2 386.5 43% : 57%SQL 4.0 7.1 2,048 38.0 266.0 72% : 28%SPACE 3.9 7.0 2,016 37.4 261.8 65% : 35%SQLPLAN 2.3 4.1 1,184 22.0 153.8 86% : 14%RAC 1.3 2.2 640 11.9 83.1 65% : 35%SQLTEXT 0.6 1.0 288 5.3 37.4 89% : 11%ASH 0.6 1.0 288 5.3 37.4 67% : 33%SQLBIND 0.3 0.6 160 3.0 20.8 60% : 40%**********************************(3b) Space usage within AWR Components (> 500K)**********************************COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE--------- --------- --------------------------------------------------------------------- ---------------FIXED 3.0 WRH$_SYSMETRIC_HISTORY_INDEX - 33% INDEXFIXED 3.0 WRH$_SYSMETRIC_HISTORY - 6% TABLEFIXED 2.0 WRH$_LATCH.WRH$_LATCH_195600696_0 - 56% TABLE PARTITIONFIXED 2.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_195600696_0 - 53% INDEX PARTITIONFIXED 0.9 WRH$_LATCH_PK.WRH$_LATCH_195600696_0 - 78% INDEX PARTITIONFIXED 0.8 WRH$_SYSSTAT.WRH$_SYSSTA_195600696_0 - 85% TABLE PARTITIONFIXED 0.7 WRH$_PARAMETER_PK.WRH$_PARAME_195600696_0 - 80% INDEX PARTITIONFIXED 0.7 WRH$_PARAMETER.WRH$_PARAME_195600696_0 - 85% TABLE PARTITIONFIXED 0.6 WRH$_SYSMETRIC_SUMMARY - 8% TABLEFIXED 0.6 WRH$_WAITCLASSMETRIC_HIST_IND - 29% INDEXFIXED 0.6 WRH$_WAITCLASSMETRIC_HISTORY - 13% TABLEEVENTS 2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__195600696_0 - 59% INDEX PARTITIONEVENTS 0.9 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__195600696_0 - 85% TABLE PARTITIONEVENTS 0.5 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_195600696_0 - 63% TABLE PARTITIONSQL 2.0 WRH$_SQLSTAT.WRH$_SQLSTA_195600696_0 - 47% TABLE PARTITIONSPACE 0.6 WRH$_SEG_STAT.WRH$_SEG_ST_195600696_0 - 80% TABLE PARTITIONSQLPLAN 2.0 WRH$_SQL_PLAN - 65% TABLESQLTEXT 0.5 WRH$_SQLTEXT - 74% TABLE**********************************(4) Space usage by non-AWR components (> 500K)**********************************COMPONENT MB SEGMENT_NAME SEGMENT_TYPE--------- --------- --------------------------------------------------------------------- ---------------NON_AWR 4.0 SYSMAN.MGMT_MESSAGES TABLENON_AWR 4.0 SYSTEM.SYS_LOB0000001147C00009$$ LOBSEGMENTNON_AWR 2.0 SYSMAN.MGMT_METRICS TABLENON_AWR 2.0 SYSMAN.MGMT_MESSAGES_PK INDEXNON_AWR 2.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEXNON_AWR 1.0 SYS.SYS$SERVICE_METRICS_TAB TABLENON_AWR 1.0 SYS.SYS_LOB0000006339C00038$$ LOBSEGMENTNON_AWR 0.9 SYSMAN.MGMT_METRICS_PK INDEXNON_AWR 0.9 SYS.SYS_LOB0000005097C00005$$ LOBSEGMENTNON_AWR 0.9 SYSMAN.MGMT_METRICS_IDX_03 INDEXNON_AWR 0.9 SYSMAN.MGMT_METRICS_IDX_01 INDEXNON_AWR 0.8 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY TABLENON_AWR 0.8 SYSMAN.SYS_LOB0000015321C00004$$ LOBSEGMENTNON_AWR 0.6 SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS TABLENON_AWR 0.6 SYSMAN.MGMT_METRICS_RAW_PK INDEXNON_AWR 0.6 SYSMAN.MGMT_POLICIES TABLENON_AWR 0.6 SYS.I_WRI$_OPTSTAT_HH_ST INDEXNON_AWR 0.5 SYSMAN.MGMT_JOB_STEP_PARAMS TABLENON_AWR 0.5 SYSMAN.MGMT_POLICY_ASSOC_CFG TABLENON_AWR 0.5 SYSMAN.PK_MGMT_JOB_STEP_PARAMS INDEXNON_AWR 0.5 SYS.SYS_LOB0000006331C00004$$ LOBSEGMENT**********************************(5a) AWR snapshots - last 50**********************************Total snapshots in DB 195600696 Instance 2 = 1Total snapshots in DB 195600696 Instance 1 = 1 DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------ 195600696 20 1 +00000 00:00:13.4 07:52:14 (06/01) 07:42:19 (06/01) 0 0 195600696 20 2 +00000 00:00:11.8 07:52:15 (06/01) 07:41:20 (06/01) 0 0**********************************(5b) AWR snapshots with errors or invalid**********************************no rows selected**********************************(5c) AWR snapshots -- OLDEST Non-Baselined snapshots********************************** DBID INST SNAP_ID ENDTM STATUS ERROR_COUNT---------- ----- ---------- ----------------- ------ ----------- 195600696 1 20 07:52:14 (06/01) 0 0**********************************(6) AWR Control Settings - interval, retention********************************** DBID LSNAPID LSPLITID LSNAPTIME LPURGETIME FLAG INTERVAL RETENTION VRSN----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ---- 195600696 20 0 06/01 07:52:28 06/01 08:11:13 2 +00000 01:00:00.0 +00008 00:00:00.0 5**********************************(7a) AWR Contents - row counts for each snapshots********************************** SNAP_ID INST ASH SQL SQBND FILES SEGST SYSEVT---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- 20 1 3 99 257 5 55 155 20 2 12 105 300 5 58 156**********************************(7b) AWR Contents - average row counts per snapshot**********************************SNAP_COUNT INST ASH SQLSTAT SQLBIND FILES SEGSTAT SYSEVENT---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- 1 2 12 105 300 5 58 156 1 1 3 99 257 5 55 155**********************************(7c) AWR total item counts - names, text, plans********************************** SQLTEXT SQLPLAN SQLBMETA SEGOBJ DATAFILE TEMPFILE---------- ---------- ---------- ---------- ---------- ---------- 407 4506 1267 297 5 1########################################################(II) Advisor Framework Info########################################################**********************************(1) Advisor Tasks - Last 50**********************************OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK 07:52:03 (05/20) AUTO INITIALSYS/ADDM 24/ADDM:195600696_3 11:00:19 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 12/ADDM:195600696_1_3 11:00:19 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 23/ADDM:195600696_2_3 11:00:19 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 13/ADDM:195600696_1_4 12:00:23 (05/20) 1 1 AUTO COMPLETEDSYS/ADDM 25/ADDM:195600696_2_4 12:00:24 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 26/ADDM:195600696_4 12:00:24 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 14/ADDM:195600696_1_5 13:00:29 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 28/ADDM:195600696_5 13:00:29 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 27/ADDM:195600696_2_5 13:00:29 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 29/ADDM:195600696_2_6 14:00:17 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 15/ADDM:195600696_1_6 14:00:17 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 16/ADDM:195600696_6 14:00:17 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 17/ADDM:195600696_1_7 15:00:21 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 30/ADDM:195600696_2_7 15:00:21 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 18/ADDM:195600696_7 15:00:21 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 20/ADDM:195600696_8 16:00:34 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 19/ADDM:195600696_1_8 16:00:34 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 31/ADDM:195600696_2_8 16:00:34 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 32/ADDM:195600696_2_9 17:00:40 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 42/ADDM:195600696_9 17:00:40 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 41/ADDM:195600696_1_9 17:00:40 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 33/ADDM:195600696_2_10 18:00:51 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 44/ADDM:195600696_10 18:01:15 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 43/ADDM:195600696_1_10 18:01:15 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 45/ADDM:195600696_1_11 19:00:01 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 34/ADDM:195600696_2_11 19:00:01 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 35/ADDM:195600696_11 19:00:01 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 36/ADDM:195600696_2_12 20:00:02 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 46/ADDM:195600696_1_12 20:00:02 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 47/ADDM:195600696_12 20:00:02 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 37/ADDM:195600696_2_13 21:00:09 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 48/ADDM:195600696_1_13 21:00:09 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 49/ADDM:195600696_13 21:00:09 (05/20) 9 9 AUTO COMPLETEDSYS/ADDM 50/ADDM:195600696_1_14 22:00:15 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 38/ADDM:195600696_2_14 22:00:15 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 51/ADDM:195600696_14 22:00:15 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 39/ADDM:195600696_2_15 23:00:21 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 52/ADDM:195600696_1_15 23:00:21 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 53/ADDM:195600696_15 23:00:21 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 54/ADDM:195600696_1_16 00:00:25 (05/21) 0 0 AUTO COMPLETEDSYS/ADDM 40/ADDM:195600696_2_16 00:00:25 (05/21) 0 0 AUTO COMPLETEDSYS/ADDM 55/ADDM:195600696_16 00:00:25 (05/21) 0 0 AUTO COMPLETEDSYS/ADDM 57/ADDM:195600696_17 01:00:28 (05/21) 0 0 AUTO COMPLETEDSYS/ADDM 56/ADDM:195600696_1_17 01:00:28 (05/21) 0 0 AUTO COMPLETEDSYS/ADDM 61/ADDM:195600696_2_17 01:00:28 (05/21) 0 0 AUTO COMPLETEDSYS/ADDM 62/ADDM:195600696_2_18 02:00:32 (05/21) 0 0 AUTO COMPLETEDSYS/ADDM 59/ADDM:195600696_18 02:00:32 (05/21) 0 0 AUTO COMPLETEDSYS/ADDM 58/ADDM:195600696_1_18 02:00:32 (05/21) 0 0 AUTO COMPLETEDSYS/ADDM 71/ADDM:195600696_19 03:00:41 (05/21) 1 1 AUTO COMPLETEDSYS/ADDM 60/ADDM:195600696_1_19 03:00:41 (05/21) 0 0 AUTO COMPLETEDSYS/ADDM 63/ADDM:195600696_2_19 03:00:41 (05/21) 0 0 AUTO COMPLETED**********************************(2) Advisor Task - Oldest 5**********************************OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------SYS/ADDM 11/ADDM:195600696_1_2 10:00:13 (05/20) 1 1 AUTO COMPLETEDSYS/ADDM 21/ADDM:195600696_2_2 10:00:13 (05/20) 1 1 AUTO COMPLETEDSYS/ADDM 22/ADDM:195600696_2 10:00:14 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 23/ADDM:195600696_2_3 11:00:19 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 12/ADDM:195600696_1_3 11:00:19 (05/20) 0 0 AUTO COMPLETEDSYS/ADDM 24/ADDM:195600696_3 11:00:19 (05/20) 0 0 AUTO COMPLETED**********************************(3) Advisor Tasks With Errors - Last 50**********************************OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------TASK_DESC--------------------------------------------------------------------------------------------------------------ERROR_MSG--------------------------------------------------------------------------------------------------------------SYS/SQL Tuning 1/SYS_AUTO_SQL_TUNING_TASK 07:52:03 (05/20) AUTO INITIALDescription: Automatic SQL Tuning TaskError Msg :########################################################(III) ASH Usage Info########################################################**********************************(1a) ASH histogram (past 3 days)********************************************************************(1b) ASH histogram (past 1 day)********************************************************************(2a) ASH details (past 3 days)********************************************************************(2b) ASH details (past 1 day)********************************************************************(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)**********************************Foreground %Background %MMNL %End of ReportReport written to awrinfo.txtSQL>GET_STATS_HISTORY_RETENTION--------------------------- 31SQL> SQL>GET_STATS_HISTORY_AVAILABILITY---------------------------------------------------------------------------20-MAY-12 07.52.20.633129000 AM -04:00SQL> SQL>MIN(SAVTIME)---------------------------------------------------------------------------MAX(SAVTIME)---------------------------------------------------------------------------20-MAY-12 07.52.27.091525 AM -04:0020-MAY-12 06.01.15.033444 PM -04:00SQL> SQL>MIN(SAVTIME)---------------------------------------------------------------------------MAX(SAVTIME)---------------------------------------------------------------------------20-MAY-12 07.52.28.548542 AM -04:0020-MAY-12 06.01.15.071814 PM -04:00SQL> SQL>MIN(SAVTIME)---------------------------------------------------------------------------MAX(SAVTIME)---------------------------------------------------------------------------20-MAY-12 07.52.27.103629 AM -04:0020-MAY-12 06.01.15.048213 PM -04:00SQL> SQL>MIN(SAVTIME)---------------------------------------------------------------------------MAX(SAVTIME)---------------------------------------------------------------------------20-MAY-12 07.53.50.386756 AM -04:0020-MAY-12 07.57.17.388624 AM -04:00SQL> SQL>MIN(SAVTIME)---------------------------------------------------------------------------MAX(SAVTIME)---------------------------------------------------------------------------20-MAY-12 07.57.47.200604 AM -04:0020-MAY-12 07.57.47.205012 AM -04:00SQL> SQL> COUNT(*)---------- 2191SQL> SQL> COUNT(*)---------- 2631SQL> SQL> COUNT(*)---------- 21962SQL> SQL> COUNT(*)---------- 5206SQL> SQL> COUNT(*)---------- 18SQL> SQL> COUNT(*)---------- 126
若发现statistics统计信息占用了SYSAUX上的大量空间，则可以考虑 使用dbms_stats.purge_stats过程实施清理
Modify retention period: DBMS_STATS.ALTER_STATS_HISTORY_RETENTION Purge old statistics: DBMS_STATS.PURGE_STATS This procedure purges old versions of statistics saved in the dictionary. To run this procedure, you must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege. 1. Stats Retention is set to 31 days. By Default it is 7 days. I suggest you can consider reducing the retention days to 10. 2. The number of rows exits in the SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY is 42 Million Rows , where the data exists from "09-JUN-11 01.52.06.895132 PM -05:00 " to "22-AUG-11 02.53.34.754747 PM -05:00" 3. For other tables ie: SYS. WRI$_OPTSTAT_TAB_HISTORY, SYS.WRI$_OPTSTAT_HISTGRM_HISTORY, the data exists for a month, this is just because the retention set to 31. ACTION PLAN: ========= A) Purge the Snapshot . Retain data for 10 days and then purge all the other data. (The number of days data to be kept depends on your Business needs) SQL> SPOOL CHECK1.OUT 1) Try to force the execution of the purge operations : SQL> alter session set "_swrf_test_action" = 72; 2) Purging snapshots : SQL> exec dbms_stats.purge_stats(sysdate-&days); using &days = n, n-1, n-2, ..., n-x 3) Then again execute the below set of SQL's and upload the spool output file SQL> SELECT MIN(SAVTIME),MAX(SAVTIME) FROM WRI$_OPTSTAT_TAB_HISTORY; SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_TAB_HISTORY; SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY; SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY; SQL> SELECT MIN(SAVTIME), MAX(SAVTIME) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY; SQL> SELECT COUNT(*) FROM SYS.WRI$_OPTSTAT_HISTGRM_HISTORY; SQL> SPOOL OFF B) Change the No. of Retention days from 31 to 10. For performing the same, use the below command : (The new retention time is specified in minutes.) SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>14400); NOTE : The parameter value is in minutes so 10 daysx 24 hours x 60 minutes = 14400 minutes C) Check the value of STATISTICS_LEVEL If the above value is Set to ALL, then consider changing to TYPICAL. The reason for requesting to change it to TYPICAL , is because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space. Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped. Once the above actions performed, please keep us posted on the status of the same