DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: how to find the size of a table

  1. #1
    Join Date
    Jan 2003
    Posts
    141

    how to find the size of a table

    Hi,
    How to find the size of a table?


    Thanks,

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    For a simple calculation BLOCKS in DBA_TABLES will give you the number of blocks used.

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

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Hello ,

    Try this:

    analyze table [table_name] compute statistics;

    and then :

    select blocks from user_tables where table_name=[table_name];

    and multiply blocks with your block_size

    I guess this is it (?)

    HTH
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  4. #4
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    jovery, you're too fast...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Code:
    select round(sum(bytes)/1024/1024) "Size of table in MB" from dba_segments where segment_name='TABLE_NAME'
    If you want to calculate from DBA_TABLES, then you have to ANALYZE and so...

    When you get info in DBA_SEGMENTS why go for DBA_TABLES?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Your right Abhay, I misread the question (teach me for responding to fast) I thought he wanted number of used blocks in table.

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

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jovery
    I thought he wanted number of used blocks in table.
    Even that is in DBA_SEGMENTS.

    Code:
    WW04_DBA> select Blocks from dba_segments where segment_name='WKLY_ADV_DELTA_AGGR_FIS';
    
                  BLOCKS
    --------------------
                  300243
    
    WW04_DBA> select round((Blocks)*8192/1024/1024) "Size of table in MB" from dba_segments where segment_name='WKLY_ADV_DELTA_AGGR_FIS';
    
     Size of table in MB
    --------------------
                    2346
    
    WW04_DBA> select round(sum(bytes)/1024/1024) "Size of table in MB" from dba_segments where segment_name='WKLY_ADV_DELTA_AGGR_FIS';
    
     Size of table in MB
    --------------------
                    2346
    
    WW04_DBA>
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    USED blocks I said

    Now who's too fast?
    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!

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it depends, dba_segments tell the allocated size, dba_tables tells the real size

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    OS dosent see who many Blocks are used or Not..its just a logical approch for Oracle...

    Allocated Space is what OS sees...and this is at file level..(Disk Level rather)
    At file level Segment is allocated N bytes...and we can take it as size of the table.

    So (N/8192) blocks are allocated for the segment & we can well treat this as size in context to OS.

    If it boils down to Oracle application, then (N/8192) blocks will be misleading as some of the blocks allocated and not used, as seen from DBA_SEGMENTS.

    So, if you see as physical point of view, then DBA_SEGMENTS will give accurate size of the table.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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