Re: rapid growth for sysaux tablespace

SYSAUX Grows Because Optimizer Stats History is Not Purged (Doc ID 1055547.1)
Bug 14373728 - Old statistics not purged from SYSAUX tablespace (Doc ID 14373728.8)

I've run into the same issue. Manually purging stats wasn't working for me, so I truncated the table and rebuilt the indexes. I'm not suggesting you do this. I didn't care about stats history and did it as a workaround until that database gets upgraded.

SQL> truncate table WRI$_OPTSTAT_HISTGRM_HISTORY;
SQL> alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild;
SQL> alter index I_WRI$_OPTSTAT_H_ST rebuild;

On Sat, Nov 1, 2014 at 3:56 AM, Mostafa Eletriby < dmarc-noreply_at_freelists.org> wrote:

> Hello ,
> Actually I ran this query & found the top segments that consume storage.
> But don't know what should I do exactly to shrink space.
> As shown below:-
> Also I checked Support Doc 287679.1 , Please advice me.
>
> Thanks
>
>
> OWNER
> ------------------------------
> SEGMENT_NAME
>
>
> --------------------------------------------------------------------------------
> SIZE_MBS
> ----------
> SYS
> I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST
>
> 27919
>
>
> SYS
> I_WRI$_OPTSTAT_H_ST
>
> 23305
>
>
> SYS
> WRI$_OPTSTAT_HISTGRM_HISTORY
>
> 22655
>
>
> SYS
> I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
>
> 19634
>
>
> SYS
> WRI$_OPTSTAT_HISTHEAD_HISTORY
>
> 18846
>
>
> SYS
> I_WRI$_OPTSTAT_HH_ST
>
> 9027
>
>
> SYS
> I_WRI$_OPTSTAT_IND_OBJ#_ST
>
> 6372
>
>
> SYS
> WRI$_OPTSTAT_IND_HISTORY
>
> 4740
>
>
> SYS
> I_WRI$_OPTSTAT_IND_ST
>
> 4388
>
>
> SYS
> I_WRI$_OPTSTAT_TAB_OBJ#_ST
>
> 3790
>
>
> SYS
> I_WRI$_OPTSTAT_TAB_ST
>
> 2581
>
>
> SYS
> WRI$_OPTSTAT_TAB_HISTORY
>
> 2366
>
>
> SYS
> WRH$_SYSMETRIC_HISTORY
>
> 1461
>
> SYS
> WRH$_SYSMETRIC_HISTORY_INDEX
>
> 1091
>
>
> SYS
> WRH$_ACTIVE_SESSION_HISTORY
>
> 513
>
>
>
> On Thursday, October 30, 2014 5:26 PM, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>
> ​I'd check to see what segment(s) are consuming space in SYSAUX and then
> cross reference those objects in Oracle Support to see if there are known
> issues and workarounds/solutions.
>
> select owner, segment_name, bytes/1024/1024 as size_mbs
> from dba_segments
> where tablespace_name = 'SYSAUX'
> order by bytes desc
> /
>
>
> Then cross reference those objects in Oracle support...
>
> Chris
> ​
>
> On Thu, Oct 30, 2014 at 9:54 AM, Mostafa Eletriby <
> dmarc-noreply_at_freelists.org> wrote:
>
> Dear All,
> Please I have a problem regarding sysaux tablespace. it is a database for
> BI & I need to purge unused space at sysaux & all other tablespaces if
> needed. to free unused space.
> I checked these options but didn't start them yet.
> what are the procedures that I should follow in such case, as data are
> very sensitive.
>
> alter table xxx deallocate unused space;
> alter index xxx deallocate unused space;
>
> alter table xxx coalesce;
> alter index xxx coalesce;
>
> Now I started to run
> exec dbms_stats.purge_stats(sysdate-4);
> It takes 2 days & still running.
>
> DB Version: 11.2.0.2 Enterprise Edition
> DB size: about 500 GB
> O.S: Windows 2008 Enterprise Edition R2 64 Bit
>
> Please check & advice.
> Thanks
> Regards,
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l