Orphan AWR Data Consuming SYSAUX Space

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s