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.
Printable View
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.
Hi Sameer
Just wunderin what happened..to the good old pctfree parameter ;-D...and what about block overheads..;-D
well how about this
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
This document might help as well
http://support.oracle.co.uk/metalink...T&p_id=10640.1
Regards
My reply wasn't complete enough !, I take back the post :)Quote:
Originally posted by hrishy
Just wunderin what happened..to the good old pctfree parameter ;-D...and what about block overheads..;-D
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,
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
Visit our Web Site