Re: rapid growth for sysaux tablespace
- From: "Mostafa Eletriby" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "m_etrib@xxxxxxxxx" for DMARC)
- To: "justin@xxxxxxx" <justin@xxxxxxx>, "dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>
- Date: Sun, 2 Nov 2014 00:36:17 -0700
I checked the case again & I found that I have to upgrade the database in order
to purge normally.
Is there a problem or bad impact if I performed this workaround and purge the
table manually as you showed me?
I don't need any stats history. Please advice.
Regards,
On Saturday, November 1, 2014 11:13 AM, Justin Mungal <justin@xxxxxxx> wrote:
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:
