Re: Defragmenting SYSTEM tablespace

"Larry M. Jones" <ljones_at_mindseye.berkeley.edu> writes:
>ganeshk_at_aol.com (GaneshK) wrote:
>>As a result of using the default installation procedure my Oracle7 server
>>SYSTEM tablespace is fragmented (I can count atleast 10 free space extents
>>in the tablespace). We do not have any user created objects or RBS in the
>>system tablespace. Is there a way to defragment the SYSTEM tablespace.
 

>Exporting it, changing storage parameters & importing it is the cheap
>solution. There are several 3rd party DBA type tools around that will
>defrag tablespaces (don't know about SYSTEM, though). Prices vary by
>what system you're running on. Sorry, but I don't have the literature
>available any more on the companies. You should be able to find them in
>DBMS magazine or any other data base magazine.

        To defragment your SYSTEM tablespace -- without buying a third party tool -- you need to do a full export, then edit the SQL.BSQ file (which creates the internal data dictionary objects) to increase the first extent size appropriately, then recreate the database with your modified SQL.BSQ and re-import the data.

        Unless several objects in your SYSTEM tablespace have more than one extent, however, there is really no need to do this. If there are several free extents, even if they are scattered throughout the tablespace, that *won't* hurt your performance. There's no need to worry about it.

        Platinum Technology sells a utility called TSreorg, which can de-fragment any tablespace (including SYSTEM) *while the database is up*. I don't know of any other tool that does this, offhand. You can contact Platinum at 1-800-545-7528. Depending on your up-time requirements, it may be necessary to buy it.

        Hope this helps.

--
-bn
random_at_interaccess.com	(PGP 2.6.2 public key available on request)
"There is no .signature -- only ZUUL!"