Script:以下脚本可以用于诊断SYSAUX表空间使用情况
./opatch lsinventory -detail @?/rdbms/admin/awrinfo select 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 at 08:57:12 on Jun 01, 2012 ( Friday ) in Timezone -04:00 select 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% INDEX FIXED 3.0 WRH$_SYSMETRIC_HISTORY - 6% TABLE FIXED 2.0 WRH$_LATCH.WRH$_LATCH_195600696_0 - 56% TABLE PARTITION FIXED 2.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_195600696_0 - 53% INDEX PARTITION FIXED 0.9 WRH$_LATCH_PK.WRH$_LATCH_195600696_0 - 78% INDEX PARTITION FIXED 0.8 WRH$_SYSSTAT.WRH$_SYSSTA_195600696_0 - 85% TABLE PARTITION FIXED 0.7 WRH$_PARAMETER_PK.WRH$_PARAME_195600696_0 - 80% INDEX PARTITION FIXED 0.7 WRH$_PARAMETER.WRH$_PARAME_195600696_0 - 85% TABLE PARTITION FIXED 0.6 WRH$_SYSMETRIC_SUMMARY - 8% TABLE FIXED 0.6 WRH$_WAITCLASSMETRIC_HIST_IND - 29% INDEX FIXED 0.6 WRH$_WAITCLASSMETRIC_HISTORY - 13% TABLE EVENTS 2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__195600696_0 - 59% INDEX PARTITION EVENTS 0.9 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__195600696_0 - 85% TABLE PARTITION EVENTS 0.5 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_195600696_0 - 63% TABLE PARTITION SQL 2.0 WRH$_SQLSTAT.WRH$_SQLSTA_195600696_0 - 47% TABLE PARTITION SPACE 0.6 WRH$_SEG_STAT.WRH$_SEG_ST_195600696_0 - 80% TABLE PARTITION SQLPLAN 2.0 WRH$_SQL_PLAN - 65% TABLE SQLTEXT 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 TABLE NON_AWR 4.0 SYSTEM.SYS_LOB0000001147C00009$$ LOBSEGMENT NON_AWR 2.0 SYSMAN.MGMT_METRICS TABLE NON_AWR 2.0 SYSMAN.MGMT_MESSAGES_PK INDEX NON_AWR 2.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX NON_AWR 1.0 SYS.SYS$SERVICE_METRICS_TAB TABLE NON_AWR 1.0 SYS.SYS_LOB0000006339C00038$$ LOBSEGMENT NON_AWR 0.9 SYSMAN.MGMT_METRICS_PK INDEX NON_AWR 0.9 SYS.SYS_LOB0000005097C00005$$ LOBSEGMENT NON_AWR 0.9 SYSMAN.MGMT_METRICS_IDX_03 INDEX NON_AWR 0.9 SYSMAN.MGMT_METRICS_IDX_01 INDEX NON_AWR 0.8 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE NON_AWR 0.8 SYSMAN.SYS_LOB0000015321C00004$$ LOBSEGMENT NON_AWR 0.6 SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS TABLE NON_AWR 0.6 SYSMAN.MGMT_METRICS_RAW_PK INDEX NON_AWR 0.6 SYSMAN.MGMT_POLICIES TABLE NON_AWR 0.6 SYS.I_WRI$_OPTSTAT_HH_ST INDEX NON_AWR 0.5 SYSMAN.MGMT_JOB_STEP_PARAMS TABLE NON_AWR 0.5 SYSMAN.MGMT_POLICY_ASSOC_CFG TABLE NON_AWR 0.5 SYSMAN.PK_MGMT_JOB_STEP_PARAMS INDEX NON_AWR 0.5 SYS.SYS_LOB0000006331C00004$$ LOBSEGMENT ********************************** (5a) AWR snapshots - last 50 ********************************** Total snapshots in DB 195600696 Instance 2 = 1 Total 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 INITIAL SYS/ADDM 24/ADDM:195600696_3 11:00:19 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 12/ADDM:195600696_1_3 11:00:19 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 23/ADDM:195600696_2_3 11:00:19 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 13/ADDM:195600696_1_4 12:00:23 (05/20) 1 1 AUTO COMPLETED SYS/ADDM 25/ADDM:195600696_2_4 12:00:24 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 26/ADDM:195600696_4 12:00:24 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 14/ADDM:195600696_1_5 13:00:29 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 28/ADDM:195600696_5 13:00:29 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 27/ADDM:195600696_2_5 13:00:29 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 29/ADDM:195600696_2_6 14:00:17 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 15/ADDM:195600696_1_6 14:00:17 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 16/ADDM:195600696_6 14:00:17 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 17/ADDM:195600696_1_7 15:00:21 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 30/ADDM:195600696_2_7 15:00:21 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 18/ADDM:195600696_7 15:00:21 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 20/ADDM:195600696_8 16:00:34 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 19/ADDM:195600696_1_8 16:00:34 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 31/ADDM:195600696_2_8 16:00:34 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 32/ADDM:195600696_2_9 17:00:40 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 42/ADDM:195600696_9 17:00:40 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 41/ADDM:195600696_1_9 17:00:40 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 33/ADDM:195600696_2_10 18:00:51 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 44/ADDM:195600696_10 18:01:15 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 43/ADDM:195600696_1_10 18:01:15 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 45/ADDM:195600696_1_11 19:00:01 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 34/ADDM:195600696_2_11 19:00:01 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 35/ADDM:195600696_11 19:00:01 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 36/ADDM:195600696_2_12 20:00:02 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 46/ADDM:195600696_1_12 20:00:02 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 47/ADDM:195600696_12 20:00:02 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 37/ADDM:195600696_2_13 21:00:09 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 48/ADDM:195600696_1_13 21:00:09 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 49/ADDM:195600696_13 21:00:09 (05/20) 9 9 AUTO COMPLETED SYS/ADDM 50/ADDM:195600696_1_14 22:00:15 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 38/ADDM:195600696_2_14 22:00:15 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 51/ADDM:195600696_14 22:00:15 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 39/ADDM:195600696_2_15 23:00:21 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 52/ADDM:195600696_1_15 23:00:21 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 53/ADDM:195600696_15 23:00:21 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 54/ADDM:195600696_1_16 00:00:25 (05/21) 0 0 AUTO COMPLETED SYS/ADDM 40/ADDM:195600696_2_16 00:00:25 (05/21) 0 0 AUTO COMPLETED SYS/ADDM 55/ADDM:195600696_16 00:00:25 (05/21) 0 0 AUTO COMPLETED SYS/ADDM 57/ADDM:195600696_17 01:00:28 (05/21) 0 0 AUTO COMPLETED SYS/ADDM 56/ADDM:195600696_1_17 01:00:28 (05/21) 0 0 AUTO COMPLETED SYS/ADDM 61/ADDM:195600696_2_17 01:00:28 (05/21) 0 0 AUTO COMPLETED SYS/ADDM 62/ADDM:195600696_2_18 02:00:32 (05/21) 0 0 AUTO COMPLETED SYS/ADDM 59/ADDM:195600696_18 02:00:32 (05/21) 0 0 AUTO COMPLETED SYS/ADDM 58/ADDM:195600696_1_18 02:00:32 (05/21) 0 0 AUTO COMPLETED SYS/ADDM 71/ADDM:195600696_19 03:00:41 (05/21) 1 1 AUTO COMPLETED SYS/ADDM 60/ADDM:195600696_1_19 03:00:41 (05/21) 0 0 AUTO COMPLETED SYS/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 COMPLETED SYS/ADDM 21/ADDM:195600696_2_2 10:00:13 (05/20) 1 1 AUTO COMPLETED SYS/ADDM 22/ADDM:195600696_2 10:00:14 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 23/ADDM:195600696_2_3 11:00:19 (05/20) 0 0 AUTO COMPLETED SYS/ADDM 12/ADDM:195600696_1_3 11:00:19 (05/20) 0 0 AUTO COMPLETED SYS/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 INITIAL Description: Automatic SQL Tuning Task Error 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 Report Report written to awrinfo.txt SQL> GET_STATS_HISTORY_RETENTION --------------------------- 31 SQL> SQL> GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- 20-MAY-12 07.52.20.633129000 AM -04:00 SQL> SQL> MIN(SAVTIME) --------------------------------------------------------------------------- MAX(SAVTIME) --------------------------------------------------------------------------- 20-MAY-12 07.52.27.091525 AM -04:00 20-MAY-12 06.01.15.033444 PM -04:00 SQL> SQL> MIN(SAVTIME) --------------------------------------------------------------------------- MAX(SAVTIME) --------------------------------------------------------------------------- 20-MAY-12 07.52.28.548542 AM -04:00 20-MAY-12 06.01.15.071814 PM -04:00 SQL> SQL> MIN(SAVTIME) --------------------------------------------------------------------------- MAX(SAVTIME) --------------------------------------------------------------------------- 20-MAY-12 07.52.27.103629 AM -04:00 20-MAY-12 06.01.15.048213 PM -04:00 SQL> SQL> MIN(SAVTIME) --------------------------------------------------------------------------- MAX(SAVTIME) --------------------------------------------------------------------------- 20-MAY-12 07.53.50.386756 AM -04:00 20-MAY-12 07.57.17.388624 AM -04:00 SQL> SQL> MIN(SAVTIME) --------------------------------------------------------------------------- MAX(SAVTIME) --------------------------------------------------------------------------- 20-MAY-12 07.57.47.200604 AM -04:00 20-MAY-12 07.57.47.205012 AM -04:00 SQL> SQL> COUNT(*) ---------- 2191 SQL> SQL> COUNT(*) ---------- 2631 SQL> SQL> COUNT(*) ---------- 21962 SQL> SQL> COUNT(*) ---------- 5206 SQL> SQL> COUNT(*) ---------- 18 SQL> 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
