Re: Unfragment SYSTEM Tablespace

Mark D Powell wrote:
>
> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote:
> >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.
> >>
> >> -----------------------------------------------------------
> >>
> >(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
> >
> I know that in reality some of the base tables are basically
> stand-alone and that you can actually run exp/imp on them and
> everything still work; however, Oracle does not support this.
>
> We have had system corruption more than once and each time the
> entry level support person has told us we had to rebuild our
> database but with a little begging and a more experienced
> support person we were able to get direction to use DML to
> correct the situation. Executing DML against the dictionary is
> dangerous enough but running DDL against a dictionary object is
> extremely dangerous and in some cases fatal to the health of the
> database. I stick to the official line when I see questions on
> reorganizing the system tablespace because if you do not have
> support on the phone providing direction you have no business
> attempting to manipulate Oracle's internals. You are just
> asking for trouble.
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com

hence my first sentence...

"(From a theoretical standing here)"

:-)  

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

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