Re: System tablespace growing

From: Frank <fvanbortel_at_netscape.net>
Date: Mon, 28 Apr 2003 22:57:37 +0200
Message-ID: <3EAD95C1.4040500@netscape.net>

Chuck wrote:
> 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.
>

And all users, with the exception of SYS, have tablespace, but SYSTEM as their default tablespace!

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

I see that popping up in scripts every now and then, and it's totally unnecessary to create rollback segments in the system tablespace before creating tablespaces.
And it has been since 7.1, iirc.
>
> 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'
> /
>

Hmmm - I'd leave it. If you ever run in a situation where objects in SYSTEM generate an out-of-space error, you're stuck. Some objects can be changed, though, especially for those versions not supporting SYSTEM as LMT (you happen to have such a beast - congrats!)

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

One thing I have missed: is anyone doing development of PL/SQL code? All code resides in the SYSTEM tablespace; source$ table. Some "surrounding" tables get hit, too, like ub1$, ub2$. And a 50% increase on those will matter... You could change them to e.g. next 1M pct_increase 0

-- 
Regards, Frank van Bortel

Received on Mon Apr 28 2003 - 15:57:37 CDT