Oracle Database Discussions

Hi,

As per the blog post http://querycsm.wordpress.com/2013/02/16/dba_data_files-dba_free_space-and-dba_segmentsa-myth-buster/

DBA_DATA_FILES=DBA_SEGMENTS+HEADER+DBA_FREE_SPACE

As DBA_SEGMENTS doesn't show up space occupied by header, DBA_DATA_FILES-DBA_SEGMENTS < DBA_FREE_SPACE.

But I'm seeing a weird scenario in one of my databases where DBA_DATA_FILES-DBA_SEGMENTS is much less than DBA_FREE_SPACE

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files where tablespace_name='CSM' group by tablespace_
name;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
CSM 500

SQL> select tablespace_name tsfs,sum(bytes)/1048576 used_spac from dba_segments where tablespace_name ='CSM' group by
tablespace_name;

TSFS USED_SPAC
------------------------------ ----------
CSM 408

SQL> select tablespace_name tsfs,sum(bytes)/1024/1024 free_space from dba_free_space where tablespace_name='CSM' grou
p by tablespace_name;

TSFS FREE_SPACE
------------------------------ ----------
CSM 291


Would any one explain how's this possible?

CSM