Skip to Content
avatar image
Former Member

Question - changing the NEXT extend size manually

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 11, 2007 at 05:19 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 11, 2007 at 05:26 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded