Re: System Tablespace gone mad!

We've done some digging around, and the I_SOURCE1 index is an index on the SOURCE$ table. The SOURCE$ table is under 200K, so having an index of 500Mb seems to make little sense!

Rich.

Sybrand Bakker wrote:

> "Richard Q Woodland" <richwoodland_at_magicinterface.com> wrote in message
> news:3BE1850E.5CF6F16C_at_magicinterface.com...
> > A few days ago, the system tablespace on our development server
> > filled-up, and hit a half gig. At that time, we doubled the system
> > tablespace to over a gig. Today, it's back to being filled up.
> >
> > The load on this server, and the amount of development work being done
> > has not changed in any significant way over this period of time.
> >
> > The chief space hogs in system seem to be SYS.IDL_UB2$ (500+ Mb) and
> > SYS.I_SOURCE1 (614Mb).
> >
> > I can't imagine what a handful of users could possibly generate enough
> > PL/SQL source code to consume a half gig in three days!!
> >
> > Can anybody think of a scenario which might cause these two entities to
> > start to ballon in size?
> >
> > Thanks (in advance) for any comments!
> >
> > Rich Woodland.
> > Magic Interface, Ltd.
> >
> >
>
> IIRC both objects are indexes. The index is created at database creation
> time, and especially when you imported your data into this database, your
> index will be degraded from the start. It will degrade further due to
> inserts and deletes.
> Could you run
> analyze index ... validate structure;
> select blocks, del_lf_rows, pct_used
> from index_stats
> on *both* indexes
> (index_stats will always contain one row)
> Probably those indexes need to be rebuild.
>
> Hth
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address
Received on Thu Nov 01 2001 - 13:32:41 CST