How to calculate table + index size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to calculate table + index size

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    138

    How to calculate table + index size

    I need to calculate (in gigabytes) sum of the table and index size for a particular table. Is there a way to find out this? Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    select sum(bytes)/1024/1024/1024 ||'Gb' from dba_extents where segment_name = 'XXX';

  3. #3
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    select sum(bytes)/1024/1024/1024 ||'Gb' from dba_extents where segment_name = 'XXX';

    this shows the extents allocated to the table.

    is there any way to find how much of the extents
    are occupied with rows?

    -Raja

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    When a table has been analyzed you can get statistics to answer your question from the dba_tables, user_tables, or all_tables views.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by rajabalachandra
    select sum(bytes)/1024/1024/1024 ||'Gb' from dba_extents where segment_name = 'XXX';

    this shows the extents allocated to the table.

    is there any way to find how much of the extents
    are occupied with rows?
    Nope. At least not without trace-dumping all the blocks belonging to the particular segment and inspecting those dumps.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Nope. At least not without trace-dumping all the blocks belonging to the particular segment and inspecting those dumps.
    Why not...and why do u wana go for dumping all blocks to know which extent has how many rows.. crazy idea.. when it can be done in a single query ...


    Code:
    Select 
      Count(*) 
    From 
      My_Table 
    Where 
      Dbms_Rowid.Rowid_Relative_Fno(Rowid) = My_Extent's_Relative_File_Number
    ;
    
    
    Or possibally
    
    Select 
      Segment_Name  ,
      Extent_Id     ,
      A.CNT
    From
    (
    Select 
      Dbms_Rowid.Rowid_Relative_Fno(Rowid) RFN ,
      Count(*) CNT
    From
      My_Table
    Group By
      Dbms_Rowid.Rowid_Relative_Fno(Rowid)
    ) A,
    DBA_EXTENTS
    Where
      Segment_Name = 'My_Table' And
      RELATIVE_FNO  = A.RFN
    ;
    
    Ofcourse this will eat up CPU.
    Hope i am correct in reading the requirement.

    Abhay.
    Last edited by abhaysk; 02-18-2004 at 09:54 AM.
    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"

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    [BWhy not...and why do u wana go for dumping all blocks to know which extent has how many rows.. crazy idea.. [/B]
    Khm, not so crazy. In fact, as I said, it's the only way to find out if the block contains any data or not (for tables, anyway; indexes are even more complicated).

    Neither of your queries will display correct result with 100% accuracy, some blocks that actually contain data might not be encountered by your queries.

    Speaking about crazy ideas - the only crazy idea in this thread was the requirement to find which blocks contain any data and which do not. I mean, even if it can be done, what would the result tell you anyway?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Neither of your queries will display correct result with 100% accuracy, some blocks that actually contain data might not be encountered by your queries.
    The chances of err would be very very less..and that to only in cases of row chains/migration..
    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