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