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: Defrag Tablespaces and pctincrease 1

Re: Defrag Tablespaces and pctincrease 1

From: Colin Polykett <polyketc_at_wgw.bibip1.bt.co.uk>
Date: 1997/02/06
Message-ID: <5dceb3$rm9@pheidippides.axion.bt.co.uk>#1/1

In article <01bc13b7$cdf54f00$454948a6_at_7l603>, "Scott T. Johnson" <sjohnson_at_ibm.net> wrote:
>I am creating a new database and would like to create tablespaces
>that are easy to defrag. I once read that setting pctincrease to 1 in the
>storage clause when creating the tablespace will have Oracle automatically
>defrag the tablespaces.
>
>Is there any truth to this? What are the drawbacks?
>
>Thanks,
>Scott

Yes this is true. If default PCTINCREASE is 0 then SMON will not auto-coalesce the free space. If default PCTINCREASE is non-0 then it will.

Drawbacks : You may not want to coalesce the free space in tablespaces used for temporary segments (its faster to have free extents already split into the correct size).

In 7.3 there is an 'alter tablespace <tsname> coalesce' command which will coalesce freespace on demand regardless of the default PCTINCREASE.

In versions 7.0 - 7.3 you can use 'alter session set events 'immediate trace name coalesce level x'' (where x =65536*number of free extents + ts# from sys.ts$ for this tablespace).

Hope this helps,

Colin.



The opinions expressed above have probably been pinched from somewhere else as I have none of my own.

Colin Polykett polyketc_at_bibip1.wgw.bt.co.uk CSO Open Systems ORACLE DBA Received on Thu Feb 06 1997 - 00:00:00 CST

Original text of this message

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