Space usage Estimation

Join Date
Sep 2000
Location
Chennai, India
Posts
865

Space usage Estimation

Given a table structure, the database block size and the number of rows it would contain, how can we estimate the space used by such a table ?

Is there a formula for this ?

Thanks.

Join Date
Jan 2001
Posts
2,828
Hi Sameer

Just wunderin what happened..to the good old pctfree parameter ;-D...and what about block overheads..;-D

1)create the table
2)insert representative rows in the table
3)analyze the table
4)multiple the avg_row_len * num_of_rows
from the dba_tables view..then you can extrapolate...

search this forum for table sizing excel spread sheet for other techniques..There is also a excel sheet somwhere on this forum for table sizing..which can calculate this for you..I have never used it though..

regards
Hrishy

3. This document might help as well

Regards

4. Originally posted by hrishy

Just wunderin what happened..to the good old pctfree parameter ;-D...and what about block overheads..;-D
My reply wasn't complete enough !, I take back the post

5. Junior Member
Join Date
Jan 2003
Location
Tegucigalpa, Honduras
Posts
4
This script may be usefull to estimate real-space utilization.

set echo off
set serveroutput on
set verify off
accept v_user prompt 'Schema: '
accept v_object prompt 'Object: '
accept v_object_type prompt 'Object Type: '
declare
VAR1 number;
VAR2 number;
VAR3 number;
VAR4 number;
VAR5 number;
VAR6 number;
VAR7 number;
VAR8 number;
vtable varchar2(30);

begin
dbms_space.unused_space(upper('&v_user'),upper('&v_object'),upper('&v_object_type'),VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7);
VAR2 := (VAR2)/1024/1024;
VAR4 := (VAR4)/1024/1024;
VAR8 := (VAR2-VAR4);
dbms_output.put_line('OBJECT_NAME = '|| upper('&v_object'));
dbms_output.put_line('------------------------------');
dbms_output.put_line('TOTAL_BLOCKS = '||VAR1);
dbms_output.put_line('TOTAL_MBYTES = '||VAR2);
dbms_output.put_line('UNUSED_BLOCKS = '||VAR3);
dbms_output.put_line('UNUSED_MBYTES = '||VAR4);
dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||VAR5);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||VAR6);
dbms_output.put_line('LAST_USED_BLOCK = '||VAR7);
dbms_output.put_line('TOTAL_USED_MBYTES = '||VAR8);
end;
/
set verify on
set echo on

Regards,

6. You have to know the size of the row in bytes, or at least the avg of it. A VARCHAR2(2000) column in the row could store once 2 bytes and once 150 bytes.

You have to load a sample of your data into a test table, get the avg row size and then calculate. The bigger the sample - the better.

Hope that helps,

clio_usa - OCP 8/8i/9i DBA