Re: System tablespace growing

From: Nate Jones <nathan.jones_at_bi-tech.com>
Date: 30 Apr 2003 13:49:45 -0700
Message-ID: <cde4fecc.0304301249.23739074@posting.google.com>

Greetings all!

Thanks so very much for all the replies! I am skipping around a bit in your replies ( I have tried most of the others and will reply to them shortly).

Richard,

I ran your sql and came up with:

SQL> select * from (select owner,segment_name,bytes/1024/1024 MB

  2                       from dba_segments
  3                       where TABLESPACE_NAME = 'SYSTEM'
  4                       order by MB desc)

  5 where rownum < 11;

SYS
I_COL1
97.6171875

SYS
C_OBJ#
83.1328125

SYS
I_COL2
 47.015625

SYS
I_COL3
 44.140625

SYS
IDL_UB1$
41.5078125

SYS
VIEW$
34.4609375

SYS
SOURCE$
17.7109375

SYS
I_SOURCE1
    8.8125

SYS
ACCESS$
     8.125

SYS
C_COBJ#
  7.828125

10 rows selected.

Col$ and its indexes seem to be one of the culprits. That table is growing very fast. It is currently 1,012,393 rows; that is about 100,000 greater than last week. What is that table?

A couple of the other tables look suspect, too, but I'm not sure if that is ok or not.

Nate

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<QNNra.25516$1s1.388438_at_newsfeeds.bigpond.com>...
> "Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message
> news:blrra.10890$pK2.14154_at_news.indigo.ie...
> > what's wrong with just
> >
> >
> > select * from (select owner,segment_name,bytes/1024/1024 MB from
> > dba_segments where TABLESPACE_NAME = 'SYSTEM' ) where rownum < 11 order by
> > MB desc;
> >
> > This gives you the top 10 space hoggers in SYSTEM
> >
>
> Hi Telmachus,
>
> Actually it doesn't.
>
> It gives you the first ten selected segments, ordered by size which is not
> the same thing.
>
> To list the top ten largest segments, rewrite as follows:
>
> select * from (select owner,segment_name,bytes/1024/1024 MB
> from dba_segments
> where TABLESPACE_NAME = 'SYSTEM'
> order by MB desc)
> where rownum < 11;
>
> Cheers
>
> Richard
Received on Wed Apr 30 2003 - 15:49:45 CDT