Problem solve Get help with specific problems with your technologies, process and projects.

Calculating next extent size

In an Oracle environment, is there a formula that you can follow for calculating the "next extent size"? Also, is Oracle 9i going to allow online database reorganiztion? Quest software currently has a tool that does that.

I've seen a few formulas floating around that say to plug in some values and I'll tell you what you next extent size. But in my opinion, these formulas all lack certain information. The biggest question you need to ask is what is my table's growth rate, and how much do I want to allocate when I run out of space? For instance, assume my table is currently 1 MB in size. I might know that my table will grow at the rate of 1 MB per year. Do I want to allocate next year's growth in one shot, or in two? If I want to allocate all of next year's data in one shot, then NEXT=1M. If I want to do this every quarter, then NEXT=250K. Hopefully, you get the idea.

One thing to keep in mind is that most DBAs are going away from painstaking sizing of INITIAL and NEXT for each and every database segment. Oracle 8i introduced Locally Managed Tablespaces(LMT). Many Oracle DBAs are using LMTs with UNIFORM extent sizes. With this, all objects in the tablespace will have all extents sized one way.

One of Oracle 9i's newest features is the ability to perform online reorganization of database objects. The DBMS_REDEFINITION package facilitates this for you. For an example, you can see a white paper I wrote on Oracle 9i's new features on my Web site. I give an example of how to use this package to perform an online reorganization.

For More Information

Dig Deeper on Oracle database design and architecture