Data de Inclusão:
02/09/2006 | Última Alteração:
02/09/2006
Tipo:Scripts | Enviada por:
Moderador Oracle_br (moderador@oraclebr.com.br)
Estimating Table Size
This tip comes from Sameer Wadhwa, senior database administrator for NuGenesis Technologies Corp in West Borough, MA.
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 OBJE
CT
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@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;