Thank you a lot. I will perform the operation. Also I would like to ask if I should truncate those other tables too , if needed. or truncate any other tables that may need to be done ? truncate table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY; truncate table SYS.WRI$_OPTSTAT_TAB_HISTORY; truncate table SYS.WRI$_OPTSTAT_IND_HISTORY; As to free space at sysaux as they have huge storage. Please advice. Regards, On Sunday, November 2, 2014 9:41 AM, Justin Mungal <justin@xxxxxxx> wrote: I had no problems, but you should take a backup before doing it. On Sun, Nov 2, 2014 at 1:36 AM, Mostafa Eletriby <m_etrib@xxxxxxxxx> wrote: 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, >> >> >> > > >