The Oracle (tm) Users' Co-Operative FAQ
dba_segments and dba_tables show different numbers for blocks. What has gone wrong ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 15th Dec 2002 Oracle version(s): 7 - 9 |
| When I look at the views dba_segments and dba_tables, they show difference values for the number of blocks used for a table. Why ? |
This is not a problem.
The dba_segments view tells you about blocks that has been allocated (reserved) in the database for an object. The dba_tables view will be telling you how many blocks from that space had been used the last time you ran the analyze command, or used the dbms_stats package to collect statistics about the table.
Note - the blocks figure recorded in the dba_tables view tells you about the point that the highwater mark for the table had reached. If you delete all the rows from a table, the highwater mark will not move, so the number of blocks used will not (from this perspective) be changed, even when you re-analyze the table. However, if you truncate the table the highwater mark will be reset to zero, and an immediate analyze would show the blocks figures back at zero.
In passing - the dbms_stats package has been the 'advised'' method for collecting CBO-related statistics about object in recent versions of Oracle (probably since 8.1, but perhaps a little earlier).
Further reading: FAQ: What's the difference between DELETE ROWS and TRUNCATE TABLE ?
