Re: rapid growth for sysaux tablespace

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