Why is DBA_SEGMENTS presenting twice the amount of data than the DBA_TABLES?

Question

I tried to compute the audit log table usage using the following steps:

1. Truncate all the audit log table data (i.e. AUD$)
2. Perform test case that inserts and update data, also delete statement but it wont delete anything.

3. Measure the byte used on the dba segment.

   select tablespace_name,bytes from dba_segments
   where tablespace_name ='SYSTEM'
   and SEGMENT_NAME = 'AUD$';
   

4. Recompute of statistical data.

5. Retrieve the byte used in the statistical data.

   select NUM_ROWS * AVG_ROW_LEN as BYTE_USED
   from DBA_TABLES where TABLE_NAME = 'AUD$';
   

The value I get from #3 is more than twice that of #5, Why is it allocating too much?

Answer 1

Your select on dba_tables doesn't take into account:

• Empty blocks in the table (see initial/next, minextents and freelist-related storage parameters among others)
• Empty space in the blocks (due to pct_free mainly)
• Block headers (initrans influences this size, among others)

I.e. it doesn't take into account the physical storage of the data at all. dba_segments on the other hand only counts physical blocks for the segment, regardless of how much data they contain.

A table with a pct_free set to 50% and never updated will have statistically half-empty blocks for example, and the calculation you did on dba_tables will reveal that when compared to dba_segments.

See the Logical Storage Structures docs to learn more about how data is stored in your database (and the overhead involved).