Re: Oracle Database Sizing

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 23 Feb 2006 06:48:38 -0800
Message-ID: <1140706118.355705.30350@t39g2000cwt.googlegroups.com>

To expand on Ken's response. If you need to make the estimate before the tables are build and test data or better yet some real sample data can be loaded then

For single byte character sets

Each date is 7 bytes for the date plus a null indicator so 8 bytes

Each varchar2 column less than 128 bytes is should be defined as the average length of its contents plus 1 byte for the length. For lengths of 128 bytes and up I believe that you need 3 bytes for the lengh/null indicator.

Number are roughly the average number of digits / 2 plus 1, but negative numbers need an extra byte.

Every row has a 3 byte header and every data block has both fixed and variable overhead. Using 104 would not be a bad guess.

If you take the average row size times the expected number you can then divide by the Oracle block size minus the overhead to get an idea of how many blocks the table will require. You can then adjust that for your storage allocation method: uniform extents, autoallocate, or dictionary.

HTH -- Mark D Powell -- Received on Thu Feb 23 2006 - 08:48:38 CST