Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: PCTINCREASE and the SYSTEM tablespace

Re: PCTINCREASE and the SYSTEM tablespace

From: <>
Date: Wed, 20 Sep 2000 07:37:34 -0400
Message-Id: <>

Fragmentation and the avoidance thereof is one of those religious issues among DBAs, and I'm surprised your message didn't awake a storm of controversy on the list. I can only guess that many of the DBAs are away on defragmentation pilgrimages and so did not reply.

As to the issue of PCTINCREASE 0 in the system tablespace, it has been set to 50 in the sql.bsq script going way back. When I last installed 8.1.6 for Linux I was happy to see that it was set to 0 in bsq.sql. There are a lot of good reasons not to mess with this whole issue (especially if you migrate databases and discover that something you changed in SYSTEM has poisoned your migration in a later version of the RDBMS. However I lot of experienced DBAs who I respect do in fact change the value in sql.bsq before creating the DB and have not reported anything horrible happening (beyond the usual range of horrible things that happen in the course of life). Resetting it to 0 immediately after the objects are created would not necessarily be a complete way to avoid fragmentation from various sizes of extents in one tablespace.

In short, fragmentation and the avoidance thereof is one of those religious issues among DBAs...

Chris Gait

On 12 Sep 2000, at 10:20, Allan Robertson wrote:

> I have just started in my new place of work and we were discussing the
> pctincrease/smon/coalesce issue for tablespaces. The new place prefers to
> have pctincrease=0 and coalesce via a cron cobtrooled script. Fair enough
> ...
> We then came to discuss the pctincrease value for the system TS. I have
> always left it at 50% for new sys object creation.
> At my new place they build the data dict/apps etc then set it to 0%.
> I am getting mixed input from books, web sites and metalink on setting the
> value of pctincrease on the system TS, (including Steve Adams site & his
> custonizing sql.bsq doc). This is probably the wrong question to ask of a
> group of DBAs but ...... does anyone have any view on the subject?
> Thanks
> Allan
> ***********************************************************************
> Allan Robertson
> Morse Business Applications
> Switch : 0131 338 6060
> Fax : 0131 338 6061
> Email :
> This email and any attachments are confidential and are intended only for
> the addressee. If you are not the
> intended recipient of this email and have received it in error, please
> notify the sender immediately by
> reply email and then delete it from your system.
> Orders are accepted subject to Morse's January 2000 terms and conditions.
> Copies are available at <> and on
> request.
> ***********************************************************************
Received on Wed Sep 20 2000 - 06:37:34 CDT

Original text of this message