Re: rapid growth for sysaux tablespace
- From: Justin Mungal <justin@xxxxxxx>
- To: dmarc-noreply@xxxxxxxxxxxxx
- Date: Sat, 1 Nov 2014 04:11:32 -0500
You are running into a bug in 11.2.0.2. See:
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@xxxxxxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxx> 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,
>
>
>
>
>
Other related posts:
