Re: data block stockage capacity

It is truly impossible to calculate the exact "storage capacity" for an Oracle database block, for several reasons:

• some data structures (ITLs, table chart, row chart, etc) grow and shrink due to the current state of transactions on rows in the block
• uncommited DELETEs and UPDATEs which shrink a row "reserve" their space until COMMIT or ROLLBACK, again forming a dependency on the current state of transactions on rows in the block
• most importantly, most Oracle datatypes (except DATEs and CHARs) are variable-length, adjusting the length dependent on data values

PCTUSED is merely a threshold value, the percentage value under which used-space in the block must fall in order to return to the free list.

Personally, I just tend to round the DB_BLOCK_SIZE down to the nearest "000" (i.e. 8192 down to 8000) in my own mind. It's far from scientific and far from exact, but when it's impossible to be exact, who cares?

If you are trying to estimate how much space X rows of a specific table are going to consume, the best (and easiest) method is extrapolation. Obtain a relatively small sample of data (i.e. 10,000 rows), insert them into a table previously truncated (with DROP STORAGE) and then ANALYZE COMPUTE the table. The value of BLOCKS is the number of blocks populated. The value of "#-rows/BLOCKS" is your average density of rows per block, so take the number of rows you eventually expect (i.e. "X") and divide that by the average density of rows per block to get the expected number of blocks.

Sure, the last populated block in the table might be "short" a few rows, so if you feel like correcting for that, you can do so by querying FILE# and BLOCK# from the ROWID and doing a GROUP BY to COUNT(*) the number of rows per block. Usually you'll find that the highest block is a little short by "Y" rows, so recalculate using something like "(10,000 - Y) / (BLOCKS - 1)"...

It's just as accurate as any other method and a helluva lot faster and easier to calculate.

Hope this helps...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, April 08, 2002 6:58 AM

> How can I calculate the appropriate stocakge capacity space for a
> block (8K).
>
> It is exact to use data BLOCk_SIZE*(PCTUSED/100).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Andrey Bronfin
> INET: andreyb_at_elrontelesoft.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Bernard, Gilbert
> INET: Gilbert.Bernard_at_caissedesdepots.fr
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).