Hi all!
Some time ago I faced the this situation in a client. SYSAUX was simply growing with no reason, but retention and everything has not changed.
After some research, could map issue to Bug 14084247: STBH: ORA-01555 DUE TO WRH$_ACTIVE_SESSION_HISTORY NOT PURGED.
There is an one-ooff patch, but needs also extra steps to make it work, as stated on MOS: Excessive AWR Growth From Partitioned Objects Such as SYS.WRH$_EVENT_HISTOGRAM Causing Sysaux to Grow (Doc ID 1912201.1):
However, even after applying bug patch 14084247, this fix will not have an immediate effect and may take some time to "catch up".
The good new is that this can also be applied in on-line patching mode. I did another post documenting this patch applying: Online Data Patch Apply with multiple Databases on same Oracle Home: OPatch failed with error code 26
Some other relevant MOS Documents under this subject are:
– WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)
– Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)
Ok, but waht can I do right now?
You can purge the top AWR tables, as described on MOS: WRH$_LATCH, WRH$_SYSSTAT, and WRH$_PARAMETER Consume the Majority of Space within SYSAUX (Doc ID 2099998.1).
Check steps below on how it can be done:
1) Checking for SYSAUX Retention/Usage and Top Objects/Segments:
[oracle@GREPORA-SRVR ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 10 16:42:26 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ORA11 > SELECT retention FROM dba_hist_wr_control;
RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0
SYS@GREPORADB > set lines 200
set pages 100
col segment_name format a30;
col mb format 99,990.0;
col segment_type format a20;
select * from (select 'AWR ' as component, segment_name,
sum(bytes)/1024/1024 as MB,
segment_type
from (select segment_name, bytes,segment_type,
(case when segment_type like '%TABLE%' then bytes else 0 end) as tbytes,
(case when segment_type like '%INDEX%' then bytes else 0 end) as ibytes
from dba_segments
where (segment_name like 'WRH%' or segment_name like 'WRM%')
and tablespace_name = 'SYSAUX'
and owner = 'SYS') where bytes > 500000
group by segment_name,segment_type
order by sum(bytes) desc) where rownum<=10;SYS@GREPORADB > SYS@GREPORADB > SYS@GREPORADB > SYS@GREPORADB > SYS@GREPORADB > SYS@GREPORADB > 2 3 4 5 6 7 8 9 10 11 12
COMP SEGMENT_NAME MB SEGMENT_TYPE
---- ------------------------------ --------- --------------------
AWR WRH$_EVENT_HISTOGRAM_PK 308.7 INDEX PARTITION
AWR WRH$_EVENT_HISTOGRAM 300.6 TABLE PARTITION
AWR WRH$_LATCH 286.6 TABLE PARTITION
AWR WRH$_SQLSTAT 220.6 TABLE PARTITION
AWR WRH$_SYSSTAT_PK 195.6 INDEX PARTITION
AWR WRH$_SYSSTAT 187.6 TABLE PARTITION
AWR WRH$_PARAMETER_PK 171.2 INDEX PARTITION
AWR WRH$_LATCH_PK 171.2 INDEX PARTITION
AWR WRH$_ACTIVE_SESSION_HISTORY 162.0 TABLE PARTITION
AWR WRH$_PARAMETER 144.6 TABLE PARTITION
10 rows selected.
2) And now deleting orphaned data from those tables
SYS@GREPORADB > SELECT COUNT(1) HOW_MANY
FROM sys.WRH$_PARAMETER a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
); 2 3 4 5 6 7 8 9
HOW_MANY
----------
329472
SYS@GREPORADB > set timing on
SYS@GREPORADB > DELETE FROM sys.WRH$_PARAMETER a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot b
WHERE b.snap_id = a.snap_id
AND dbid=(SELECT dbid FROM v$database)
AND b.dbid = a.dbid
AND b.instance_number = a.instance_number); 2 3 4 5 6 7 8
329472 rows deleted.
Elapsed: 00:00:04.58
SYS@GREPORADB > commit;
Commit complete.
Elapsed: 00:00:00.00
SYS@GREPORADB > DELETE FROM sys.WRH$_SYSSTAT a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot b
WHERE b.snap_id = a.snap_id
AND dbid=(SELECT dbid FROM v$database)
AND b.dbid = a.dbid
AND b.instance_number = a.instance_number); 2 3 4 5 6 7 8
635544 rows deleted.
Elapsed: 00:00:09.40
SYS@GREPORADB > commit;
Commit complete.
Elapsed: 00:00:00.00
SYS@GREPORADB > SELECT COUNT(1) HOW_MANY
FROM sys.WRH$_EVENT_HISTOGRAM a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
); 2 3 4 5 6 7 8 9
HOW_MANY
----------
1103039
Elapsed: 00:00:00.81
SYS@GREPORADB > DELETE FROM sys.WRH$_EVENT_HISTOGRAM a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot b
WHERE b.snap_id = a.snap_id
AND dbid=(SELECT dbid FROM v$database)
AND b.dbid = a.dbid
AND b.instance_number = a.instance_number); 2 3 4 5 6 7 8
1103039 rows deleted.
Elapsed: 00:00:17.50
SYS@GREPORADB > commit;
Commit complete.
Elapsed: 00:00:00.01
SYS@GREPORADB > SELECT COUNT(1) HOW_MANY
FROM sys.WRH$_SQLSTAT a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
); 2 3 4 5 6 7 8 9
HOW_MANY
----------
76410
Elapsed: 00:00:00.15
SYS@GREPORADB > DELETE FROM sys.WRH$_EVENT_HISTOGRAM a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot b
WHERE b.snap_id = a.snap_id
AND dbid=(SELECT dbid FROM v$database)
AND b.dbid = a.dbid
AND b.instance_number = a.instance_number); 2 3 4 5 6 7 8
0 rows deleted.
Elapsed: 00:00:00.83
SYS@GREPORADB > DELETE FROM sys.WRH$_SQLSTAT a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot b
WHERE b.snap_id = a.snap_id
AND dbid=(SELECT dbid FROM v$database)
AND b.dbid = a.dbid
AND b.instance_number = a.instance_number); 2 3 4 5 6 7 8
76410 rows deleted.
Elapsed: 00:00:04.42
SYS@GREPORADB > commit;
Commit complete.
Elapsed: 00:00:00.01
SYS@GREPORADB > DELETE FROM sys.WRH$_LATCH a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot b
WHERE b.snap_id = a.snap_id
AND dbid=(SELECT dbid FROM v$database)
AND b.dbid = a.dbid
AND b.instance_number = a.instance_number); 2 3 4 5 6 7 8
544752 rows deleted.
Elapsed: 00:00:10.35
SYS@GREPORADB > commit;
Commit complete.
Elapsed: 00:00:00.01
SYS@GREPORADB >
*) Additional Information:
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql).
They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql @$ORACLE_HOME/rdbms/admin/awrrpti.sql
The Output in my case is below. It can be used to define what to shrink.
~~~~~~~~~~~~~~~
AWR INFO Report
~~~~~~~~~~~~~~~
Report generated at 16:49:04 on Apr 10, 2017 ( Monday ) in Timezone +00:00
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
------------ --------- ---------------------------------------- ----- ----------------- ------------ ---
* 848056966 GREPORADB GREPORA-SRVR - Linux x86 64-bit 1 09:19:38 (02/18) 35973821 NO
########################################################
(I) AWR Snapshots Information
########################################################
*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size 3,479.1 MB ( 68% of 5,120.0 MB MAX with AUTOEXTEND ON )
|
| Schema SYS occupies 3,463.4 MB ( 99.5% )
| Schema SYSTEM occupies 14.8 MB ( 0.4% )
| Schema DBSNMP occupies 0.9 MB ( 0.0% )
|
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SM/AWR SYS 3,289.5 MB
| SM/OPTSTAT SYS 96.9 MB
| SM/ADVISOR SYS 35.6 MB
| LOGMNR SYSTEM 13.4 MB
| SM/OTHER SYS 6.2 MB
| JOB_SCHEDULER SYS 4.9 MB
| SMON_SCN_TIME SYS 3.3 MB
| PL/SCOPE SYS 2.6 MB
| SQL_MANAGEMENT_BASE SYS 1.7 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.9 MB
| AUTO_TASK SYS 0.3 MB
| AUDIT_TABLES SYS 0.0 MB
| EM SYSMAN 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
| WM WMSYS 0.0 MB
| XDB XDB 0.0 MB
| XSAMD OLAPSYS 0.0 MB
|
| Others (Unaccounted space) 18.5 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 18.5 MB
|
| Total space 18.5 MB
|
*************************************
(2) Size estimates for AWR snapshots
*************************************
|
| Estimates based on 60 mins snapshot INTERVAL:
| AWR size/day 379.6 MB (16,194 K/snap * 24 snaps/day)
| AWR size/wk 2,656.9 MB (size_per_day * 7) per instance
|
| Estimates based on 24 snaps in past 24 hours:
| AWR size/day 379.6 MB (16,194 K/snap and 24 snaps in past 24 hours)
| AWR size/wk 2,656.9 MB (size_per_day * 7) per instance
|
**********************************
(3a) Space usage by AWR components (per database)
**********************************
COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%
--------- --------- ------ ------------ ---------- ----------- ----------------
FIXED 1,817.6 55.3 8,948 209.7 1,468.0 52% : 48%
EVENTS 763.3 23.2 3,758 88.1 616.5 52% : 48%
SQL 322.7 9.8 1,589 37.2 260.6 69% : 31%
ASH 188.6 5.7 928 21.8 152.3 87% : 13%
SPACE 184.8 5.6 910 21.3 149.2 67% : 33%
SQLPLAN 6.0 0.2 30 0.7 4.8 67% : 33%
SQLTEXT 0.6 0.0 3 0.1 0.5 90% : 10%
RAC 0.6 0.0 3 0.1 0.5 50% : 50%
SQLBIND 0.4 0.0 2 0.1 0.4 57% : 43%
**********************************
(3b) Space usage within AWR Components (> 500K)
**********************************
COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE
--------- --------- --------------------------------------------------------------------- ---------------
FIXED 280.0 WRH$_LATCH.WRH$_LATCH_848056966_0 - 6% TABLE PARTITION
FIXED 192.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_848056966_0 - 14% INDEX PARTITION
FIXED 184.0 WRH$_SYSSTAT.WRH$_SYSSTA_848056966_0 - 9% TABLE PARTITION
FIXED 168.0 WRH$_PARAMETER_PK.WRH$_PARAME_848056966_0 - 13% INDEX PARTITION
FIXED 168.0 WRH$_LATCH_PK.WRH$_LATCH_848056966_0 - 16% INDEX PARTITION
FIXED 144.0 WRH$_PARAMETER.WRH$_PARAME_848056966_0 - 7% TABLE PARTITION
FIXED 136.0 WRH$_LATCH_MISSES_SUMMARY.WRH$_LATCH__848056966_0 - 94% TABLE PARTITION
FIXED 128.0 WRH$_LATCH_MISSES_SUMMARY_PK.WRH$_LATCH__848056966_0 - 99% INDEX PARTITION
FIXED 60.0 WRH$_SERVICE_STAT_PK.WRH$_SERVIC_848056966_0 - 63% INDEX PARTITION
FIXED 37.0 WRH$_ROWCACHE_SUMMARY.WRH$_ROWCAC_848056966_0 - 97% TABLE PARTITION
FIXED 32.0 WRH$_MVPARAMETER.WRH$_MVPARA_848056966_0 - 97% TABLE PARTITION
FIXED 32.0 WRH$_SERVICE_STAT.WRH$_SERVIC_848056966_0 - 96% TABLE PARTITION
FIXED 24.0 WRH$_MVPARAMETER_PK.WRH$_MVPARA_848056966_0 - 98% INDEX PARTITION
FIXED 24.0 WRH$_ROWCACHE_SUMMARY_PK.WRH$_ROWCAC_848056966_0 - 98% INDEX PARTITION
FIXED 23.0 WRH$_SERVICE_WAIT_CLASS_PK.WRH$_SERVIC_848056966_0 - 65% INDEX PARTITION
FIXED 18.0 WRH$_SERVICE_WAIT_CLASS.WRH$_SERVIC_848056966_0 - 94% TABLE PARTITION
FIXED 15.0 WRH$_SYSMETRIC_HISTORY - 92% TABLE
FIXED 13.0 WRH$_SGASTAT.WRH$_SGASTA_848056966_0 - 91% TABLE PARTITION
FIXED 12.0 WRH$_SGASTAT_U.WRH$_SGASTA_848056966_0 - 98% INDEX PARTITION
FIXED 11.0 WRH$_SYSMETRIC_HISTORY_INDEX - 91% INDEX
FIXED 7.0 WRH$_OSSTAT.WRH$_OSSTAT_848056966_0 - 85% TABLE PARTITION
FIXED 7.0 WRH$_SYS_TIME_MODEL.WRH$_SYS_TI_848056966_0 - 95% TABLE PARTITION
FIXED 7.0 WRH$_WAITSTAT.WRH$_WAITST_848056966_0 - 96% TABLE PARTITION
FIXED 7.0 WRH$_WAITSTAT_PK.WRH$_WAITST_848056966_0 - 97% INDEX PARTITION
FIXED 6.0 WRH$_SYS_TIME_MODEL_PK.WRH$_SYS_TI_848056966_0 - 97% INDEX PARTITION
FIXED 6.0 WRH$_OSSTAT_PK.WRH$_OSSTAT_848056966_0 - 97% INDEX PARTITION
FIXED 3.0 WRH$_SYSMETRIC_SUMMARY - 89% TABLE
FIXED 3.0 WRH$_RSRC_CONSUMER_GROUP - 83% TABLE
FIXED 2.0 WRH$_DB_CACHE_ADVICE.WRH$_DB_CAC_848056966_0 - 70% TABLE PARTITION
FIXED 2.0 WRH$_LATCH.WRH$_LATCH_848056966_9850 - 53% TABLE PARTITION
EVENTS 296.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__848056966_0 - 13% INDEX PARTITION
EVENTS 288.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__848056966_0 - 9% TABLE PARTITION
EVENTS 88.0 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_848056966_0 - 92% TABLE PARTITION
EVENTS 53.0 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_848056966_0 - 97% INDEX PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__848056966_9908 - 65% INDEX PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__848056966_9884 - 65% INDEX PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__848056966_9850 - 95% INDEX PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__848056966_9980 - 46% TABLE PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__848056966_9956 - 46% TABLE PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__848056966_9932 - 46% TABLE PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__848056966_9908 - 46% TABLE PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__848056966_9884 - 46% TABLE PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__848056966_9850 - 66% TABLE PARTITION
EVENTS 2.0 WRH$_ENQUEUE_STAT_PK - 56% INDEX
EVENTS 2.0 WRH$_ENQUEUE_STAT - 69% TABLE
EVENTS 2.0 WRH$_BG_EVENT_SUMMARY_PK - 54% INDEX
EVENTS 2.0 WRH$_BG_EVENT_SUMMARY - 59% TABLE
EVENTS 2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__848056966_9932 - 58% INDEX PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__848056966_9956 - 58% INDEX PARTITION
EVENTS 2.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__848056966_9980 - 64% INDEX PARTITION
EVENTS 0.7 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__848056966_10004 - 90% INDEX PARTITION
EVENTS 0.6 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__848056966_10004 - 89% TABLE PARTITION
SQL 216.0 WRH$_SQLSTAT.WRH$_SQLSTA_848056966_0 - 4% TABLE PARTITION
SQL 59.0 WRH$_SQLSTAT_PK.WRH$_SQLSTA_848056966_0 - 13% INDEX PARTITION
SQL 39.0 WRH$_SQLSTAT_INDEX.WRH$_SQLSTA_848056966_0 - 13% INDEX PARTITION
SQL 1.0 WRH$_SQLSTAT.WRH$_SQLSTA_848056966_9850 - 89% TABLE PARTITION
SQL 0.8 WRH$_SQLSTAT.WRH$_SQLSTA_848056966_9932 - 86% TABLE PARTITION
SQL 0.8 WRH$_SQLSTAT.WRH$_SQLSTA_848056966_9956 - 85% TABLE PARTITION
SQL 0.7 WRH$_SQLSTAT.WRH$_SQLSTA_848056966_9884 - 83% TABLE PARTITION
SQL 0.7 WRH$_SQLSTAT.WRH$_SQLSTA_848056966_9908 - 86% TABLE PARTITION
SQL 0.7 WRH$_SQLSTAT.WRH$_SQLSTA_848056966_9980 - 88% TABLE PARTITION
ASH 160.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_848056966_0 - 95% TABLE PARTITION
ASH 24.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_848056966_0 - 72% INDEX PARTITION
ASH 0.7 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_848056966_9850 - 84% TABLE PARTITION
ASH 0.7 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_848056966_9908 - 83% TABLE PARTITION
ASH 0.6 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_848056966_9932 - 86% TABLE PARTITION
SQL 0.7 WRH$_SQLSTAT.WRH$_SQLSTA_848056966_9980 - 88% TABLE PARTITION
ASH 160.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_848056966_0 - 95% TABLE PARTITION
ASH 24.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_848056966_0 - 72% INDEX PARTITION
ASH 0.7 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_848056966_9850 - 84% TABLE PARTITION
ASH 0.7 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_848056966_9908 - 83% TABLE PARTITION
ASH 0.6 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_848056966_9932 - 86% TABLE PARTITION
SPACE 112.0 WRH$_SEG_STAT.WRH$_SEG_ST_848056966_0 - 99% TABLE PARTITION
SPACE 55.0 WRH$_SEG_STAT_PK.WRH$_SEG_ST_848056966_0 - 64% INDEX PARTITION
SPACE 6.0 WRH$_TABLESPACE_STAT.WRH$_TABLES_848056966_0 - 93% TABLE PARTITION
SPACE 3.0 WRH$_TABLESPACE_STAT_PK.WRH$_TABLES_848056966_0 - 84% INDEX PARTITION
SPACE 0.5 WRH$_SEG_STAT.WRH$_SEG_ST_848056966_9850 - 81% TABLE PARTITION
SQLPLAN 4.0 WRH$_SQL_PLAN - 44% TABLE
SQLPLAN 2.0 WRH$_SQL_PLAN_PK - 28% INDEX
SQLTEXT 0.6 WRH$_SQLTEXT - 42% TABLE
3) Shrinking Tables:
SYS@GREPORADB > alter table sys.WRH$_PARAMETER SHRINK SPACE CASCADE; Table altered. SYS@GREPORADB > alter table sys.WRH$_SYSSTAT SHRINK SPACE CASCADE; Table altered. SYS@GREPORADB > alter table sys.WRH$_EVENT_HISTOGRAM SHRINK SPACE CASCADE; Table altered. SYS@GREPORADB > alter table sys.WRH$_SQLSTAT SHRINK SPACE CASCADE; Table altered. SYS@GREPORADB > alter table sys.WRH$_LATCH SHRINK SPACE CASCADE; Table altered.
Hope it helps!
See you next week!
Thanks, it helped me to shrink sysaux a little.