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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
