Re: Unfragment SYSTEM Tablespace

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote:
>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
 

>> >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.
>>
>hence my first sentence...
>
>"(From a theoretical standing here)"
>
>:-)
>
>--
>===========================================
>Connor McDonald
>http://www.oracledba.co.uk
>

Connor, my fault. I was not disagreeing with you; I was just posting an addition for new people to make sure they understood that messing with the system tablespace objects is not a standard practice and should be done only under limited circumstances. I apologize for not making that clear in my reply. I was going to email you and request the list of tables that you got away with moving just since I am curious and in case of the day when someone does something dumb. You know like when the system admin replaces your hot backup script cron job with full disk dumps because they run faster. Ugh!

Mark.Powell_at_eds.com

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Wed Jul 19 2000 - 00:00:00 CDT