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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.