Skip to Content
avatar image
Former Member

Why is the table free space reported so different between dba_tables and dbms_space?

Hi

I have been checking the amount of free space in various tables to see what we could reorg. I used the SQL statement in Note 821687 - FAQ: Space utilization and fragmentation in Oracle to do this, which reads entries from dba_tables.

However, this relies on up to date statistics having been ran on the tables. There are some tables where (for various reasons) we have locked the stats, so we cannot get up to date information on the free space using this method.

I then came across dbms_space which I believe doesn't require up to date stats, but rather does a table scan? I used the SQL statement in Note 1295200 - Oracle 10g or higher: Space statistics based on DBMS_SPACE.

However, the results seemed wildly different, so I ran this on some tables that we HAD ran up to date stats on. However, the results are still significantly different!

Using dba_tables:

TABLE_NAME NUM_ROWS ROWLEN BLOCKS NET_MB GROSS_MB WASTED_MB TABLE_LAST_ANALYZED LONG_RAW_COLUMN_

---------------- ------------------- ------ ------------------- ------------- ------------- ------------- ------------------- ----------------

EDI40 74399346 230 72265613 17186 518817 501630 2012-12-28 12:49:38 153

i.e. 17GB actually used (NET_MB) of a 519GB table, 502GB wasted.

Using dbms_space:

GROSS_MB NET_MB UNUSED_MB CHAIN_PERCENT

---------- ---------- ---------- -------------

564607.00 349972.96 214634.04 14

i.e. 350GB of a 565GB table used, 215GB wasted.

There's a large difference there! Which one is right?!?! Why such a huge difference?

Thanks

Ross

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Jan 10, 2013 at 04:41 PM

    Oh, and I did make sure that the stats update ran on this table was a full 'Compute', not an 'Estimate'.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 11, 2013 at 10:07 AM

    As stated by Ross the difference can come from statistics, DBA tables info are refreshed by stats, DBMS package get up to date info from the segments.

    Add comment
    10|10000 characters needed characters exceeded

    • After a massive deletion you need to reorg if you want the space you have freed to be available to other segments from the same tablespace.

      Reorg will also "compact" the data avoiding to have an high number of almost empty blocks (not good for perf, many blocks to read).

      I wouldn't trust the first query at all, trying to find (I would say guess) segment size from:

      average row length * number of rows

      is far too optimistic!

      In the note SAP states that this query is made to:

      determine the tables with the - in absolute figures - largest unused space

      and not to compute the segment size.