Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

Tags:
Former Member
Not what you were looking for? View more on this topic or Ask a question