Re: System Tablespace gone mad!

Thank you, Mr. Bakker, for such a rapid reply!

IDL_UB2$ appears to be a table, not an index, so the rebuild, etc. won't work!

We did rebuild the I_SOURCE1 index (we needed to increase the system tablespace another half gig to achieve this ;) ), then performed the analyze ... and the results from the select on index_stats is :-.

BLOCKS DEL_LF_ROWS PCT_USED
64000 0 90

Why would this stuff suddenly become such a problem in a three day period, after being up for probably a year? As I said, I don't think the activity has suddenly increased.

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:27:42 CST