Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Change SYSTEM's default tablespace?

Re: Change SYSTEM's default tablespace?

From: <oratune_at_aol.com>
Date: Wed, 27 Sep 2000 01:04:54 GMT
Message-ID: <8qrh3j$qha$1@nnrp1.deja.com>

In article <8qrf0m$op8$1_at_nnrp1.deja.com>,   kal121_at_my-deja.com wrote:
> The wasted space comes from the MINEXTENT parameter. Everytime you
> create a segment (a table) as the SYSTEM user, it creates a segment
> with 50 extents. Personally, I think this is was a bad decision on the
> part of you previous DBA. You should not let objects in the database
> get to 50 extents to begin with. You should size objects properly from
> the beginning to fit into one extent to reduce fragmentation and
> increase performance. You can alter the tablespace's default storage
 to
> re-set MINEXTENTS to 1. Then, you can override the tablespace's
 default
> storage by putting storage clauses on your tables.
>
> In article <39d12f90.84099247_at_news.mindspring.com>,
> buckatl_at_yahoo.com wrote:
> > I've been looking over some database creation scripts left by the
> > previous DBA at my company and found the following:
> > Early on in the db creation, the SYSTEM user is altered to set the
> > default tablespace to USER1, which is obviously not an
> > Oracle-generated tablespace. One of the attributes of this
 tablespace
> > is minextents = 50 with extent size = 32k. When I use these scripts
> > to create a database, I end up with huge amounts of apparently
 unused
> > storage allocated to SYSTEM's tables in this tablespace. What would
> > be the benefit of doing this?
> > From my modest experience and knowledge I'm tempted to believe that
> > perhaps the SYSTEM id should be left alone.
> > Does anyone have any helpful thoughts on the topic?
> > Thank you,
> > Kenneth Buck
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

It is not unusual to alter the SYSTEM user and assign a different tablespace. In fact, from the Optimal Flexible Architecture documents from Oracle, the SYSTEM user is to be reassigned to the TOOLS tablespace. The logic for this is that many third-party tools utilize the SYSTEM user for database access and dropping these tool-related tables into the SYSTEM tablespace is not the best idea (personally I feel that the third-party application vendors should properly write their code to begin with and utilize the TOOLS tablespace explicitly, and if none exists the installation should abort). So, SYSTEM is to be assigned to the TOOLS tablespace because lazy programmers can't be bothered to write proper code, IMHO. That being said the previous DBA (?), who used the USER1 tablespace for SYSTEM, really didn't do anything wrong, with the exception of the MINEXTENTS parameter which was terribly miscalculated. The only time I have used a MINEXTENTS value greater than 1 is when I create rollback segments, and then I only go as high as 5. 50 is quite extreme, and not a little bit careless. Follow the previous posters addvice; change the MINEXTENTS value to 1 for the USER1 tablespace, alter the script as well, and you should have no more dead space problems in the USER1 tablespace.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Sep 26 2000 - 20:04:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US