Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Defrag Tablespaces and pctincrease 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.
Colin Polykett polyketc_at_bibip1.wgw.bt.co.uk CSO Open Systems ORACLE DBA Received on Thu Feb 06 1997 - 00:00:00 CST