-
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.
-
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
-
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
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
-
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,
ltabora@bancopromerica.com
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|