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.

    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?


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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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