Re: Looking for table size estimating tools.

From: M.Rapier <M.Rapier_at_shef.ac.uk>
Date: 1996/11/20
Message-ID: <56vdbv$vl@bignews.shef.ac.uk>#1/1

In article <56rgg1$b5c_at_maple.ncs.com.sg>, setiawan_at_singnet.com.sg says...

>I'm looking for Excel spread sheet or the like that can automate the
>estimation of Oracle7 table size. Would appreciate if some one willing
>to share it.

I did do it on a spreadsheet agaes ago, but you can use the statistics gathered on Oracle 7 to do the job more easily.

Below is a function that returns the size of a table based on representative data stored within it - just supply the estimated number of rows:

tabown = table owner
tabname = table name
rowest = estimated number of rows

it assumes you've loaded some data in your table & analysed it. It can be called in whatever manner you deem appropriate.

A similar function can be devised for indexes.

Cheers.
Martin.

function tabsize (tabown IN VARCHAR2, tabname IN VARCHAR2,

                  rowest IN INTEGER)
           RETURN NUMBER

is
  result number;
  blocksize integer;

  lavg_space integer;
  lavg_row_len integer;
  lnum_rows integer;

begin

• get table statistics select avg_space, avg_row_len, num_rows into lavg_space, lavg_row_len, lnum_rows from sys.dba_tables where owner=upper(tabown) and table_name = upper(tabname); --get db_block_size select value into blocksize from v$parameter where name = 'db_block_size'; -- blocksize := 2048;
• work out the result (trap 0 row errors) if lnum_rows = 0 then result := 0; else result := ceil(rowest/floor((blocksize-lavg_space)/lavg_row_len)); end if; return(round(result*blocksize,0)); exception when others then result := 0; return(result); end;

Received on Wed Nov 20 1996 - 00:00:00 CST