Re: rapid growth for sysaux tablespace
- From: "Mostafa Eletriby" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "m_etrib@xxxxxxxxx" for DMARC)
- To: "christopherdtaylor1994@xxxxxxxxx" <christopherdtaylor1994@xxxxxxxxx>, "dmarc-noreply@xxxxxxxxxxxxx" <dmarc-noreply@xxxxxxxxxxxxx>
- Date: Sat, 1 Nov 2014 01:56:11 -0700
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,
Other related posts:
