Tablespace fragmentation....

[iyyappan]

Dear all,

Can u please explain tablespace fragmentation?

How to avoid tablespace fragmentation?

Thanks,
Iyyappan.M

[nagarjuna]

Originally posted by iyyappan
Dear all,

Can u please explain tablespace fragmentation?

Please go through Oracle documentation.

[/QUOTE]
How to avoid tablespace fragmentation?

Thanks,
Iyyappan.M
[/QUOTE]

Use locally managed tablespace or set pctincrease to a value greater than zero.

[pando]

you mean set pctincrease to 0

[nagarjuna]

Originally posted by pando
you mean set pctincrease to 0

No no... I meant to say 'set pctincrease to a non zero value'. There is no negative value for pctincrease in Oracle. So, I told him to use a value higher than zero.

[pando]

well non-zero pctincrease leads to fragmentation

[nagarjuna]

Originally posted by pando
well non-zero pctincrease leads to fragmentation

I think one Oracle background process coalesces the tablespaces when we set pctincrease to a non-zero value.. ohoh.. I am slowly losing my confidence in the subject.. Should sit with books.. it's been really loong time..

[marist89]

Back to the books!

[sreddy]

SMON Coalesces tablespaces if pctincrease > 0. But thats not best idea... Prefer using LMTs else set minimum extent size, pctincrease 0.

[pando]

pctincrease > 0 == different size extents ==> leads to freespace fragmentation

if you use uniform size extents and pctincrease 0 there is not even need to coalesce the extents, free extents will be reused efficiently

[nagarjuna]

Originally posted by pando
pctincrease > 0 == different size extents ==> leads to freespace fragmentation

if you use uniform size extents and pctincrease 0 there is not even need to coalesce the extents, free extents will be reused efficiently

This is true as long as you dont override the tablespace level storage parameters with segment level settings.

Yep, non zero value for pctincrease results in freespace fragmentation. But, setting pctincrease=1 can avoid this free space fragmentation upto some level..