-
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
-
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
-
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
-
'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
-
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.
-
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.
