Re: System tablespace growing

Ok, since I hate it when people dont come back and tell everyone what fixed the problem, I will do so now.

We discovered that the largest tables in the system tablespace (col$, view$, and obj$) were also the ones growing the fastest. Col$ was getting about 50,000 new rows a day!

The problem was that we have a permanent "temp" space that our application uses, and a recent bug was introduced in the application that was not dropping these tables one the user logged out. So, since it is actually a view, the view$ was growing, as well as the col$ and obj$ tables with all the actual column names and object info.

The short term solution was just to create a script that that run the drop view statement for tables starting with SV_. (All of the views are created with SV_)

The long term solution is, of course, to fix the bug, but that is out of my control. I'm just a wannabe DBA.

Thanks so much to everyone for their help! It is much appreciated.

Nate

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<vb0fo9j1har610_at_corp.supernews.com>...
> Quite obvious (and you can verify it in sql.bsq): the location of all column
> definitions in the database.
>
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
>
> "Nate Jones" <nathan.jones_at_bi-tech.com> wrote in message
> news:cde4fecc.0304301249.23739074_at_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 Thu May 01 2003 - 11:56:14 CDT