Re: Unfragment SYSTEM Tablespace

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 Received on Tue Jul 18 2000 - 00:00:00 CDT