# Space usage Estimation

• 01-10-2003, 09:09 AM
ggnanaraj
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.
• 01-10-2003, 09:56 AM
hrishy
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
• 01-10-2003, 10:00 AM
jovery
This document might help as well

Regards
• 01-10-2003, 10:14 AM
Sameer
Quote:

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 :)
• 01-10-2003, 10:58 AM
ltabora
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,
• 01-10-2003, 07:03 PM
clio_usa
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