Re: rapid growth for sysaux tablespace

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_at_n0de.ws> 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_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