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, > > > > > > > >