Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: easy space calculation or space calculation factor

Re: easy space calculation or space calculation factor

From: Chris Hamilton <Christopher.H.Hamilton_at_usace.army.mil>
Date: 1997/08/28
Message-ID: <01bcb3ae$57598fe0$73344b9b@hqddpc.hq.usace.army.mil> 







Deborah Dawicki wrote . . .



> I'm looking for a simpler algorithm when using rowsize and # of rows and

 a


> simpler algorithm for calculating space for indexes - any s/w tools would

> be greatly appreciated or spreadsheets.  If there is a factor I could use

> to calculate the space easier - please include that also..




Here are two SQL scripts that I wrote that do the job.



calc_tab.sql calculates estimated table size requirements.
calc_ndx.sql calculates estimated index size requirements.







rem *****************************************************************
rem Name:     calc_tab.sql
rem Author:   Chris Hamilton
rem Date:     10-Dec-92.  Revised CHH, 23-May-94.  Added prompt for
rem           Block Size, changed ttitle to include table name, added
rem           NVL support for block size.
rem           20-Dec-1997, widened some columns.
rem Purpose:  This script (based on formula in DBA guide) calculates the
rem           space allotment required for an Oracle table.
rem NOTE:     Average Length refers to defined length of columns.
rem NOTE:     Critical parameter in this script is blk_size.  Change this
rem           for your blocksize when prompted at runtime.  If you don't
rem           know the blocksize, check the db_block_size parameter in your
rem           init.ora file, or run the following query:
rem           select name, value
rem           from   sys.v_$parameter
rem           where  name = 'db_block_size'


rem Usage:    sqlplus -s un/pw @calc_tab.sql

rem *****************************************************************





set verify off;


set linesize 80;


set pagesize 58;


set space 1;


set trimspool on;


prompt -------------------------------------------------;


prompt Calculate Table Space Required Program;

prompt -------------------------------------------------;


accept table_name char prompt 'Table Name:  ';
accept n_rows number prompt '# of Rows:  ';
accept n_cols number prompt '# of Columns:  ';

accept avg_len number prompt 'Average Column Length:  ';
accept pct_free number prompt 'PCTFREE value:  ';
accept blk_size number prompt 'Block Size in Bytes:  ';


accept filename char prompt 'Filename to spool to:  ';

prompt -------------------------------------------------;


prompt Working...; 



set verify off;



column blocks_req format 999,999,990 heading "# of Blocks Req";
column bytes_req format 999,999,999,999,990 heading "# of Bytes Req";
column table_name format a24 heading "Table Name";
column n_rows format 999,999,990 heading "# of Rows";
column n_cols format 990 heading "# of|Cols";

column avg_len format 99,990.0 heading "Avg Len";
column blk_size format 9990 heading "Block|Size";
column pct_free format 90 heading "Pct|Free";


column table_name new_value tname noprint;
column today new_value report_date noprint;



ttitle skip 1 report_date right "Page" sql.pno skip 2 -

       center "Estimated Space Required to Create a Table" -
       skip 2 center "Table Name:  " tname skip 2;





spool &&filename;



select initcap(sysdate) today,

       upper('&&table_name') table_name,
       &&n_rows n_rows,
       &&n_cols n_cols,
       &&avg_len avg_len,
       nvl(&&blk_size,2048) blk_size,
       &&pct_free pct_free,
       (round
       (
       (&&n_rows * (5 + &&n_cols * (1 + &&avg_len)))
       /
       ((nvl(&&blk_size,2048) - 90) * (1 - &&pct_free/100))
       )
       ) blocks_req,
       (
       round(
       ((&&n_rows * (5 + &&n_cols * (1 + &avg_len)))
       /
       ((nvl(&&blk_size,2048) - 90) * (1 - &&pct_free/100)))) 
       * nvl(&&blk_size,2048)
       ) bytes_req




from   dual;



spool off;



pause Press <Return> to continue...;







rem *****************************************************************
rem Name:     calc_ndx.sql
rem Author:   Chris Hamilton
rem Date:     10-Dec-92.  Revised CHH, 05-May-94.  Added prompt for
rem           Block Size, changed ttitle to include table and column names.
rem           20-Feb-97, widened some columns.
rem Purpose:  This script (based on formula in DBA guide) calculates the
rem           space allotment required for an Oracle index.
rem NOTE:     # of Columns and Average Length refers to defined length
rem           of Indexed columns only.
rem NOTE:     Critical parameter in this script is blk_size.  Change this
rem           for your blocksize when prompted at runtime.  If you don't
rem           know the blocksize, check the db_block_size parameter in your
rem           init.ora file, or run the following query:
rem           select name, value
rem           from   sys.v_$parameter
rem           where  name = 'db_block_size'


rem Usage:    sqlplus -s un/pw @calc_ndx.sql

rem *****************************************************************





set verify off;


set linesize 80;


set pagesize 58;


set space 1;


set feedback off;


set trimspool on;


prompt -------------------------------------------------;


prompt Calculate Index Space Required Program;

prompt -------------------------------------------------;


accept table_name char prompt 'Table Name:  ';
accept column_name char prompt 'Column Name(s):  ';
accept n_rows number prompt '# of Rows:  ';
accept n_cols number prompt '# of Columns:  ';

accept avg_len number prompt 'Average Column Length:  ';
accept pct_free number prompt 'PCTFREE value:  ';
accept blk_size number prompt 'Block Size in Bytes:  ';


accept filename char prompt 'Filename to spool to:  ';

prompt -------------------------------------------------;


prompt Working...; 



column blocks_req format 999,999,999,990 heading "# of Blocks Req";
column bytes_req format 999,999,999,999,990 heading "# of Bytes Req";
column n_rows format 999,999,990 heading "# of Rows";
column n_cols format 990 heading "# of|Cols";

column avg_len format 999,990.0 heading "Avg Len";
column blk_size format 9990 heading "Block|Size";
column pct_free format 90 heading "Pct|Free";


column table_name format a24 heading "Indexed Table";
column table_name new_value tname noprint;
column column_name new_value cname noprint;
column today new_value report_date noprint;



break on today on table_name on column_name on report;



ttitle skip 1 report_date right "Page" sql.pno skip 2 -

       center "Estimated Space Required to Create an Index" -
       skip 2 center "Table:  " tname ".  Column(s):  " cname "." skip 3;





spool &&filename; 



select initcap(sysdate) today,

       upper('&&table_name') table_name,
       upper('&&column_name') column_name,
       &&n_rows n_rows,
       &&n_cols n_cols,
       &&avg_len avg_len,
       nvl(&&blk_size,2048) blk_size,
       &&pct_free pct_free,
       (round
       (
       (&&n_rows * (11 + &&n_cols + &&avg_len))
       /
       ((nvl(&&blk_size,2048) - 90) * (1 - &&pct_free/100))
       )
       ) blocks_req,
       (
       (round
       (&&n_rows * (5 + &&n_cols * (1 + &avg_len)))
       /
       ((nvl(&&blk_size,2048) - 90) * (1 - &&pct_free/100))
       ) 
       * nvl(&&blk_size,2048)
       ) bytes_req




from   dual;



spool off;



prompt ;


pause  Press <Return> to continue...;








Chris
Received on Thu Aug 28 1997 - 00:00:00 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US