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

Thread: How big is my database??

  1. #1
    Join Date
    Feb 2001
    Posts
    99
    Been working on queries to determine the actual amount of data stored in my database, excluding sys and system schemas. I have looked at dba_tables, retreived the blocks, empty_blocks information for my tables. I have taken these numbers, multiplied by my block size, and compared to the highwater mark. The numbers just are not jiving. I thought that I could add the blocks and empty blocks value for each object, multiply by the block size, and get a number close to what I have allocated for the tablespace.

    Question 1: What numbers to do I need to be looking at to determine the amount of data in a given schema?

    Question 2: How do I compute the same figures for the indexes? I have them stored in seperate tablespaces/datafiles.

    Any help in clearing my confusion is greatly appreciated.

    Thanks

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    Try querying dba_data_files for a more high-level picture of the amount of space you are using. Also look in dba_free space to see space that is *not* used within these datafiles.

    YOu can look in dba_indexes to see info similar to what you found in dba_tables. Another place to look is dba_segments.

    Then there is also dbms_space.unused_space. Check the
    doc for how to use it...

    -John

  3. #3
    Join Date
    Feb 2001
    Posts
    99
    Thanks for the info. I had looked at the views but had not run the package. I will try that tomorrow.

    Let me throw an example at you and someone can tell me where I am falling of the logic-train.

    I have a tablespace that I have assigned to a datafile, of which the datafile is 1gig in size. Its the only ts in the datafile.

    There are 5 tables in this tablespace and 1 index. Looking at the dba_tables view, I totaled the BLOCKS count for the tables. that was 5,686 blocks. Block size is 8192bytes. So, that tells me that thre is 46megs of data in that ts. Then, the empty blocks shows a total of 771 blocks for about 6.3megs. My looks at dba_indexes tell me that I have about 8megs of index space used. So, if I have done the looks right, I have used approxiately 61megs of a 1gig datafile? I am interpreting the 771 free blocks as what is left in the current extents for the objects. I would probably see that number move up and down as extents are allocated and used.

    So, am I interpreting the numbers correctly?

    Thanks

  4. #4
    Join Date
    Nov 2000
    Posts
    344
    'Blocks' includes 'empty blocks'.

    so if you add up all of the 'blocks' and group it by tablespace,
    then that is what extents are allocated in teh tablespace. anything left is overhead (a very small amount) or free space.

    So it sounds like from your example you have used

    46mb + sum(blocks from dba_indexes) * 8192

    of your tablespace.

    Yes you'll see that 771 change and exents are allocated and used up (assuming you re-analyze your tables!)

    -John

  5. #5
    Join Date
    Jun 2001
    Posts
    29
    dba_tables.blocks are those blocks that are below the High Water Mark. dba_tables.empty_blocks are those blocks that are allocated to the 'table' that are above the High Water Mark. The high water mark is the last block in the last extent of a segment that has been formatted for data. It is not always an accurate indication of space usage (v. allocated).

    To get a rough estimation of the size of the database,

    select sum(bytes)/1048576 megs
    from dba_segments
    where owner not like 'SYS%';

    This will include all allocated blocks. It is not exact as it will include the space devoted to objects that are seriously overallocated, but it does indicate space that is used or reserved for use.
    Daniel W. Fink
    oracledba@ix.netcom.com

  6. #6
    Join Date
    Feb 2001
    Posts
    99
    thanks for the query against dba_segments. Should that number compare with the sum of the "used" values from OEM storage manager? I have about a 7gigs of "used" space listed there but the above query of dba_segments only gave me 5.8gigs. Guess the deviation could be the shrinkage of areas, colesce of extents, deletion of some data, etc.

    a sum of the blocks values for my three main schemas (other than sys,system) show 635 megs used and 4gigs allocated.

    Guess I will use the blocks as a guestimate for now. I am trying to get some idea of growth, etc to help plan for future server config.

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