Author's name: Jonathan Lewis
Author's Email: Jonathan@jlcomp.demon.co.uk
Date written: 31-Jan-2001
Oracle version(s): 7.3 - 220.127.116.11
The views DBA_SEGMENTS and DBA_TABLES give me different numbers of blocks in my table. Why is this, and how do I actually find out how much space my table is actually using ?
The view DBA_SEGMENTS summarises the list of extents that have been allocated to a data object in the database. Once an extent has been allocated, no other object can use that space unless the extent is dropped or trimmed using either the drop object or deallocate unused syntax. If you create a table:
create table big_waste(id number, description varchar2(1000))
storage (initial 100M next 100M minextents 2)
then you are 'using' 200MB of the total space in your database, even if you have just one row of data stored in the table. If you spot the problem, you can (in most recent versions of Oracle) issue a command like:
alter table big_waste deallocate unused;
alter table big_waste deallocate unused keep 1M;
The first option will detach any extents that are above the high water mark and also above the value for the storage parameter minextents. The second is more agressive - it will detach as much space above the high-water mark as it can, reducing the table's value for minextents if necessary, trying to bring the storage down until only the given amount (in this case 1 megabyte) is left free above the high water mark.
Of course, there is a good argument for not doing this sort of thing. It is generally becoming accepted that ease of database management is more important than saving a little space, and that using a uniform size of extent within each tablespace is the best way of achieving this target.Consequently if you discover an error like the big_waste table above, the solution is not to trim the excess off, but to move the table into a tablespace with a more appropriate unit size.
Moving on to the difference between the number of blocks reported in dba_segments and dba_tables (say) the reason why these values differ is because the value in dba_tables (or the similarly used leaf_blocks in dba_indexes) is filled after you issue a command like:
analyze table big_waste estimate statistics;
Depending on your version of Oracle, this will also populate columns called blocks, empty_blocks, num_freelist_blocks. The blocks value is the number of blocks below the highwater mark - if you deallocated the wasted space as much as possible, this is the number of blocks (less one) that you would find left in the data segment. Empty_blocks is the number of blocks above the high water mark, and is the maximum number of blocks that you can eliminate with the deallocate command. Num_freelist_blocks is the number of blocks on the segment's free lists - most of these blocks will be below the high water mark, and there presence there idicates that enough data has been deleted from them that the volume of data left in each block has dropped below the storage paramete pctused. It is the free list blocks that make it difficult to determine accurately how much space is actually required by your data segment, since a block on the free list may be using anything between 0% and pctused of a block. However, by doing an estimate statistics of a table, you generally get a good idea of how much space is actually used. Note: you only need to estimate statistics for these three figures to be calculated accurately.
Of course, this only helps for tables - for indexes you could do the same and look at leaf_blocks. This is a fairly good guide, usually within about 2%, of the actual storage required by the index, but it does not cater for branch blocks, nor does it cater for blocks on free lists.
To get more accurate statistics on data objects, you usually need to do a compute statistics, or an estimate statistics with a reasonably large percentage estimate. In the case of indexes, the best bet is to do a validate index then look at the view index_stats rather than the view dba_indexes. These options, however, lock the base object and can be very expensive to run.
Further reading: A final option for getting a good indication of size (and this works for some objects which do not fall under the umbrella of the analyze command) is to use the dbms_space package which can report the number of blocks on free lists, and the number of blocks above the high-water mark for most kinds of object.