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