Re: rapid growth for sysaux tablespace
From: Mostafa Eletriby <m_etrib_at_yahoo.com>
Date: Sun, 2 Nov 2014 01:51:46 -0700
Message-ID: <1414918306.98434.YahooMailNeo_at_web162401.mail.bf1.yahoo.com>
Date: Sun, 2 Nov 2014 01:51:46 -0700
Message-ID: <1414918306.98434.YahooMailNeo_at_web162401.mail.bf1.yahoo.com>
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_at_n0de.ws> 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_at_yahoo.com> 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_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-lReceived on Sun Nov 02 2014 - 09:51:46 CET