Re: System tablespace growing

On 28 Apr 2003 11:49:42 -0700, Nate Jones wrote:

> If anyone has any other suggestions/comments, please let me know. Or
> am I worrying over nothing (only if it will stop growing!)?
>
> Thanks in advance,
>
> Nate

All good suggestions. Here a a few more.

First, change those users who are using SYSTEM as their temporary tablespace and point them to a different one. Preferable a tablespace set up as a temp tablespace from the start and using tempfiles instead of datafiles.

Second, check *DBA_SEGMENTS* for objects in SYSTEM. DBA_TABLES will not show indexes, rollback segments, or any other type of segment other than tables in the SYSTEM tablespace.

select owner, segment_name, segment_type from dba_segments
where tablespace_name = 'SYSTEM'
/

On more than one occastion I have found rollback segments in there. With the exception of the SYSTEM rbs, get them out. They are probably left over from database creation, or were accidentally imported into that tablespace if you ever did a full import and did not create the tablespace they were originally exported out of first.

Third, check for segments in SYSTEM with a pct_increase > 0. There's no need for this and it will cause the SYSTEM tablespace to grow in leaps and bounds. Most likely this is your culprit.

select owner, segment_name, segment_type from dba_segments
where pct_increase > 0 and tablespace_name = 'SYSTEM' /

Alter those segments to a pct_increase of 0. It won't reclaim any space they are holding on to, but it should stem the tide of growth. Received on Mon Apr 28 2003 - 14:44:10 CDT