This is the scipt written by Samir Wadhva. Tweek it a little bit, and it'll
work for you.
HTH,
Vladimir Ivanovich
rem blocksize * (rows in 12 month * avg row length)
rem ----------------------------------
rem (blocksize - 90) ( 1- pctfree/100)
rem
rem
rem The following formula is explained in ORACLE 8 server Adminsitor Guide
rem Appendex A Title SPACE ESTIMATION FOR SCHEMA OBJECT
rem Note Table should not be empty and it should not have following columns
Rem 'BLOB','BFILE','CLOB','LONG','LONG
RAW','MLSLABEL','NCLOB','RAW','ROWID')
rem Written By SAMEER WADHWA
rem Wadhwa_S_at_hotmail.com
rem Script should be run through SYS.
set timing off;
set verify off;
set echo off;
set feedback off;
clear screen
accept tname char prompt 'ENTER TABLE NAME >'
accept owname char prompt 'ENTER OWNER NAME >'
accept maxrows number prompt 'ENTER Approx. No. OF ROWS IN A YEAR >'
accept pctfree number prompt 'ENTER VALUE OF PCTFREE >'
rem calculate hsize
col hsize noprint new_value hs
select a.value - b.type_size - c.type_size - d.type_size - e.type_size hsize
from v$parameter a
,v$type_size b
,v$type_size c
,v$type_size d
,v$type_size e
where a.name = 'db_block_size'
and b.type = 'KCBH'
and c.type = 'UB4'
and d.type = 'KTBBH'
and e.type = 'KDBH'
/
define hhsize=&hs
col availspace noprint new_value aspace
rem calculate avg data space per data block
select ceil(&hhsize * (1- &pctfree/100)) - a.type_size availspace
from v$type_size a
where a.type = 'KDBT'
/
define aaspace=&aspace
col szze noprint new_value sz
set heading off;
set pagesize 999
set verify off;
set echo off;
set embedded on;
set feedback off;
set termout off;
spool avgrow.sql
select ' select avg(' from dual;
select 'nvl(vsize('||column_name||'),0)+' from dba_tab_columns
where table_name = '&tname'
and data_type not in ('BLOB','BFILE','CLOB','LONG','LONG
RAW','MLSLABEL','NCLOB','RAW','ROWID')
and column_id < (select max(column_id) from dba_tab_columns
where table_name = '&tname'
and data_type not in ('BLOB','BFILE','CLOB','LONG','LONG
RAW','MLSLABEL','NCLOB','RAW','ROWID')
);
select 'nvl(vsize('||column_name||'),0)) szze from
'||owner||'.'||table_name||';'
from dba_tab_columns
where table_name = '&tname'
and owner='&owname'
and data_type not in ('BLOB','BFILE','CLOB','LONG','LONG
RAW','MLSLABEL','NCLOB','RAW','ROWID')
and column_id = (select max(column_id) from dba_tab_columns
where table_name = '&tname'
and owner = '&owname'
and data_type not in ('BLOB','BFILE','CLOB','LONG','LONG
RAW','MLSLABEL','NCLOB','RAW','ROWID')
);
spool off;
@avgrow.sql
/
set termout on;
define vvsize=&sz
col rowspace noprint new_value rspace
select a.type_size * 3 + b.type_size + c.type_size + &vvsize rowspace
from v$type_size a,
v$type_size b,
v$type_size c
where a.type = 'UB1'
and b.type = 'UB4'
and c.type = 'SB2'
/
define rrspace=&rspace
select ' TABLE STORAGE STATISTICS INFORMATION :--'||chr(10)||chr(10)||
' Header size ='||&hs||chr(10)||
' Available space ='||&aspace||chr(10)||
' Row Space ='||trunc(to_char(&rspace,99999)) from
dual;
select ' Rows In A BLOCK ='||
trunc(to_char(&aaspace/&rrspace,9999)) rowsinblock from dual;
select ' Total No. of BLOCKS Req for '||
to_char(trunc(&maxrows))||' Row is '||
trunc(to_char(ceil((&maxrows*&rrspace)/&aaspace),'999999'))
from dual;
select ' Initial Extent Should be of'||
to_char(ceil((&maxrows*&rrspace)/&aaspace)*value,'9999999999999')||'
Bytes'
from v$parameter
where name='db_block_size';
select ' Total Size of Table should be'||
to_char(ceil((&maxrows*&rrspace)/&aaspace)*value,'9999999999999')||'
Bytes'
from v$parameter
where name='db_block_size';
select ' Defined PCTFREE is '||to_char(trunc(&pctfree))||' %' from
dual;
"Ken Chesak" <ken.chesak_at_dhs.state.tx.us> wrote in message
news:75f49d83.0109241054.37a8c136_at_posting.google.com...
> I would like to know the best way to estimate future table size in
> megs based on exepected number of rows. The tables are created but
> they are empty.
Received on Mon Sep 24 2001 - 14:16:22 CDT
