calculating size of a table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: calculating size of a table

Hybrid View

  1. #1
    Join Date
    Jul 2003
    Posts
    134

    calculating size of a table

    Does this forumla still work good in Oracle 10g for calculating the size of a table?

    AVG_ROW_LEN*NUM_ROWS*(1+PCT_FREE/100)*1.15

    Or is there anyother better way to calculate? Pls advice.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    select sum(bytes) from user_segments where segment_name = 'TABLE_NAME' will give you the real table size

    the above query never was accurate and was dependant on the table being analyzed right up to date

    surely you have this information since you work for oracle?

  3. #3
    Join Date
    Jul 2003
    Posts
    134
    hey thx dave. used to work for oracle, not anymore.

    and so, u r saying we need not analyze the table if i am using

    select sum(bytes) from user_segments where segment_name = 'TABLE_NAME' ??

    hmm.. big diff between this and the query i mentioned (though i was analyzed up to date)
    Last edited by Vipassana; 07-07-2005 at 04:15 AM.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    of course you need to analyze the table for the CBO to work properly! How much of a difference?

  5. #5
    Join Date
    Jul 2003
    Posts
    134
    select avg_row_len * num_rows * (1 + PCT_FREE/100) * 1.15
    from dba_tables where table_name = 'PRODUCT'
    /

    AVG_ROW_LEN*NUM_ROWS*(1+PCT_FREE/100)*1.15
    ------------------------------------------
    13925.12

    SQL> select sum(bytes) from user_segments where segment_name = 'PRODUCT';

    SUM(BYTES)
    ----------
    65536

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    So? One is (aproximately) how much space is needed to store all those rows, the other is how much space is actually allocated by that table.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jul 2003
    Posts
    134
    Quote Originally Posted by jmodic
    So? One is (aproximately) how much space is needed to store all those rows, the other is how much space is actually allocated by that table.
    I think you have added more confusion

    First of all which is which? I presume the first statement means "how much space is needed to store all those rows" and the second means "the other is how much space is actually allocated by that table".

    Secondly I didnt understand what you meant by "how much space is needed to store all those rows". What does "all the rows" mean here? You mean the rows that are existing? And also what does "how much space is needed..." mean? You mean what is the maximum space it can occupy?

    Thridly I didn't understand what you meant by "the other is how much space is actually allocated by that table". You mean how is actually used? If so "used out of what (how much)?"

    Two statements you made and they are so cryptic

  8. #8
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    Sorry for coming late to the party, but if you're using 10g you might want to take look at this:

    http://www.oracle-base.com/articles/...rce_estimation

    It takes some of the guesswork out of sizing estimations and growth trends.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  9. #9
    Join Date
    Jul 2002
    Posts
    335
    Vipassana, what do you want to know?

    How big your table is now? - use dba_segments

    How big your table could be? - use your formula and consider how many rows will be inserted and the rate they are inserted.

    Perhaps a refresh of the concepts manual is in order.

    Bazza

  10. #10
    Join Date
    Jul 2007
    Posts
    1

    Unhappy URGENT: Table size in 9i

    Hi,

    the query

    select sum(bytes) from user_segments where segment_name = 'tablename'; is giving sum(bytes) as blank, not even a zero. Please help. Does the user need dba permission to run this query?

    Please reply asap.

    Thanks and regards,
    rcm

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