Re: System Tablespace gone mad!

"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