Question - changing the NEXT extend size manually

[Former Member]

Hi Guys,

I would like to get clarification on the subjected topic. During DB reorg, we have an option to change the next extend size manulay. If I want to change the next extend size , which is the best practise to follow on deciding the next extend size? Is there any method to calculate the best next extend size?

Also, I would like to know , after a DB reorg - how continuous freespace in a tablespace changes...(i.e) DB02 --> Freespace statistics --> Tablespace --> freespace analysis

Does the continuous freespace has any direct relation with the next extend size that we choos during the DB reorg?

Please also share the link if there is any literature on this topic.

Best regards,

Vinodh.

[Former Member · Accepted solution]

Hi Vinod,

With New Versions of Oracle like 9i onwards DB reorg can be online and has featute of LMTS ( Locally managed Tablespaces) which eliminates the earlier limitaion of MAXEXTENTS and NEXT_EXTENT parameters.

However if you are operating on earlier oracle version then you need to decide on the value of NEXT_EXTENT depending on the MAXEXTENTS value. If MAXEXTENTS limit is reaching rapidly then you can set NEXT_EXTENT to higher value but with disadvantage of incompletely filled blocks and hence wasteage of space.

Freespace released doesnot hv any direct relation with next extent size. Freelists parameter will decide whether particular block is available or not.

Hope this is helpful

Regards

Umesh

[Former Member]

Hi Vinod,

It's fine to increase the MAX EXTENT, but I would also increase NEXT EXTENT to prevent the rapid increase in number of extents. If you perform a reorganization on the table, the number of extents will be reset to zero (or maybe 1).

You can take a look at the history of the table growth and how fast the number of extents are increasing through tcode DB02. If the number of extents is rapidly increasing for a table, increase NEXT EXTENT to a larger value of KB.

Hare is some doc

http://help.sap.com/saphelp_47x200/helpdata/en/1f/1cbbf5e3c3c14c82169366f1b6f629/frameset.htm

Reward points if useful

Regards,

Hari.