Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.