Re: How to find the size of a table?

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Wed, 10 Nov 1999 19:28:05 GMT
Message-ID: <01bf2bc1$98ee4da0$a504fa80@mndnet>

Hi,

Here is a script that you can use to get some numbers and multiply to arrive at the table size:

set doc off pause off wrap on  

REM

REM     Author      : Suresh N. Bhat
REM     Date        : 10/27/99
REM     File Name   : size_of_populated_table.sql
REM     Usage       : On sqlplus prompt enter:
REM                         @size_of_populated_table
REM
REM     Description : Lists Number of rows and average row length of an
REM                   already populated table.
REM
REM                   Prompt will ask for a Table name.

REM   column answer new_value answer  

accept answer char prompt 'Enter Table Name [ dual ]: '  

set termout off  

select nvl( upper('&answer'), 'DUAL') answer   from dual
/
set pagesize 500 feedback off heading off verify off trimspool on  

spool $HOME/suresh1.sql  

prompt set      pagesize 50 feedback off heading on verify off trimspool on
prompt set      termout on

prompt column avg_row_size_in_bytes format 9999.99 prompt column number_of_rows format 999999999 heading ' NUMBER OF ROWS '
prompt prompt Table Name: &answer
prompt select count(*) number_of_rows prompt from &answer
prompt /
select 'avg(nvl(vsize('||substr(lower(column_name),1,31)||'), 0))+'   from all_tab_columns
 where table_name = upper('&answer')
union
select 'select' from dual
union
select 'avg(nvl(vsize('||0||'), 0)) avg_row_size_in_bytes'   from dual
order by 1 desc
/
prompt from &answer
prompt /
spool off
@$HOME/suresh1.sql
host rm $HOME/suresh1.sql
exit

HTH Suresh Bhat
Oracleguru
www.oracleguru.net    

david_petit_at_yahoo.com wrote in article <80bpon$obk$1_at_nnrp1.deja.com>...
> Hello all,
>
> I have two questions about to determine the size of a table.
>
> 1) Is it possible to find the storage space (in bytes) of a table? And
> how accurate it is?
>
> 2) Is it possible to find the storage space (in bytes) of a set of rows
> in a table? e.g. I want to know the storage space for the following set
> of records
>
> select * from testing_table where id = 10
>
> Thanks,
> Daivd
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Wed Nov 10 1999 - 13:28:05 CST