Re: Unfragment SYSTEM Tablespace

Mark D Powell wrote:
>
> vasarpota_at_my-deja.com wrote:
> >Hi all,
> >
> >I have a problem. My SYSTEM Tablespace is fragmented because
 the
> >default Temporary and User Tablespaces were not set properly by
 another
> >DBA. How do I fix this problem?
> >
> >I have already gone through DBA_USERS and fixed the default
 tablespaces
> >that created the problem but how do I unfragment the SYSTEM
 tablespace?
> >
> >What specific options of EXPORT, IMPORT should I use to make
 sure dont
> >mess anything up?
> >
> >Thanks for your help,
> >
> >Vinnie
> >
> You don't! Per Oracle, the fact that recently used dictionary
> information is cached in the shared pool should nullify any
> effect of the fragmentation on performance. It could however
> still affect the ability of a system dictionary base table to
> extend so you might have to add another datafile to the system
> tablespace if the available free space is in non-contiguous
> extents too small to be allocted based on the current next
> settings of the base tables.
>
> The only supported method is to defrag the system tablespace is
> to recreate the database.
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com

(From a theoretical standing here)

I ran a few 'alter table ... move' commands on dictionary objects and it worked with no problems. Of course, some other ones got themselves in a nasty mess since moving them invoked some recursive sql on the same tables... Ugh!

Cheers

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse