Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




Oracle Tips by Burleson 

Sizing an Oracle9i Nonclustered Table

The procedures in this section describe how to estimate the total number of data blocks necessary to hold data inserted to a nonclustered table. Typically, the space required to store a set of rows that experience updates, deletes, and inserts will exceed this calculated value. The actual space required for complex workloads is best determined by analyzing an existing table; it is then scaled by the projected number of future rows in the production table. In general, increasing amounts of concurrent activity on the same data block results in additional overhead (for transaction records), so it is important that you take into account such activity when scaling empirical results. (Spreadsheets are available at the Wiley Web site for calculating table and index size.)


No allowance is made here for changes to PCTFREE or PCTUSED, due to insert, delete, or update activity. Thus, this reflects a best-case scenario, that is, when users insert rows without performing deletes or updates.     

       Calculating space required by nonclustered tables is a five-step process:

      1.    Calculate the total block header size.

      2.    Calculate the available data space per data block.

      3.    Calculate the space used per row.

      4.    Calculate the total number of rows that will fit in a data block.

      5.    With the rows/block data, calculate the total number of data blocks and convert to kilo- or megabytes.

A Simple Sizing Example

Let’s take a more detailed look at the steps using a simple example.

Step 1: Calculate the Total Block Header Size

The space required by the data block header is the result of the following formula:

See Code Depot


   DB_BLOCK_ SIZE. The database blocksize with which the database was created. It can be viewed in the V$PARAMETER view by selecting:

See Code Depot

KCBH, UB4, KTBBH, KTBIT, KDBH. Constants whose sizes you can obtain by selecting from entries in the V$TYPE_SIZE view.

  •  KCBH is the block common header; on NT with a 4-KB blocksize, this is 20.

  •  UB4 is “either byte 4”; on NT with a 4-KB blocksize ,this is 4.

  •  KTBBH is the transaction fixed-header length; on NT with a 4-KB blocksize, this is 48.

  •  KTBIT is transaction variable header; on NT with a 4-KB blocksize, this is 24.

  •  KDBH is the data header; on NT with a 4-KB blocksize, this is 14.

   INITRANS. The initial number of transaction entries allocated to the table.

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks