I have to reorganize few tables and I am at the point that I donít know if I should rebuild or change storage parameters or leave as they are ??!!
Based on what I read :
1. -INITIAL, NEXT parameters should be exactly the same and PCTINCREASE=0
2. INITIAL, NEXT of the table set at the same value as the INITIAL, NEXT for the tbs.
well if it doesnt take much time or you can afford downtime I would reorganize it
Your configuration in the long go would just cause fragmentation, one of the customer we have is suffering this, we told their *so-called* DBA to set initial=next pctincrease=0 he was insisting he must set a larger initial for each segment to avoid the increase of number of extents now he is suffering it badly, he has tablespace with size of 8GB and only 3 GB is occupied by real data with 5GB of space unable to be reused because of the funny extent sizes he set
Table's extent size depends upon the growth of the data.
Assume that a table grows 10 MB every month, then in a year it needs 120MB totally. You can very well set INITIAL and NEXT extent size to 30MB and PCTINCREASE 0. So in a year the table would have only 4 extents. Follow the simple principle always.
Why would you not want to set the pctincrease to something other than zero? For example, set it to 1. Oracle will coalesce the free space automatically. Would that not get rid of the 5gb of wasted space or atleast reorganize it so it can be used properly?
why would I need to coalesce if all my extents are of same size?
Click Here to Expand Forum to Full Width