Re: System Tablespace gone mad!

Thank you! We are rapidly coming to the conclusion that we may have to generate a new database instance from scratch, EXP/IMP the data between the old and new instances and then repoint the DADs of our web servers to the new one. It's peculiar, because this particular database instance it not THAT busy, from a PL/SQL development standpoint. I'm surprised that the defaults are not a little more worldly ;)

Rich Woodland
Magic Interface, Ltd.

godmann wrote:

> Hi Rich,
>
> I found something on the metalink. You may be interested but I am not sure
> how much it will help.
>
> Forum 1
> In a fresh database(newly created), SYS.Source$ has 171 extents(34MB) and
> I_Source1 has 82 extents (16MB). You can use the script from note 111156.1
> to check the size of stored objects.. (Oracle 8.1.7, Sun Solaris 2.6)
>
> Forum 2
> Question:
>
> The table SYS.SOURCE$ holds the source code for packages etc.
> There is a index on this table called I_SOURCE1. Over time this index
> must get fragmented like other Oracle indexes and therefore needs to
> be rebuilt?
>
> Answer:
>
> It is true that these indexes may become fragmented. However they
> still belong to the Data Dictionary and Oracle will not support any
> statement ran on these that drops or rebuilds them. If fragmentation
> is a concern then you would handle just like any fragmentation in other
> tables and indexes. Export compress=y, drop, recreate, and import.
> Just be aware that export does not export sys objects such as Data
> Dictionary. But when you recreate your database and begin the import,
> it will repopulate the Data Dictionary and therefore not be fragmented.
>
> my two lepta
>
> Allan W. Tham
> DBA
Received on Fri Nov 02 2001 - 06:48:21 CST