Space usage Estimation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Space usage Estimation

Hybrid View

  1. #1
    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.

  2. #2
    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

    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

  3. #3
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    This document might help as well

    http://support.oracle.co.uk/metalink...T&p_id=10640.1

    Regards
    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!

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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. #5
    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,
    ltabora@bancopromerica.com

  6. #6
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Thumbs up

    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
  •  


Click Here to Expand Forum to Full Width