The difference between our "Free" number and the total shown in DBA_FREE_SPACE is because DBA_FREE_SPACE includes objects in the Recycle Bin and our "Free" number does not. If you took the total of bytes from dba_segments, and added the total of bytes from dba_free_space, you would end up with a size larger than the tablespace size, because dba_segments includes recycle bin objects, and so does dba_free_space. Our "Free" number is total size in tablespace - total size of segments.
The results of this query in your database might help make it more clear. If you have objects in the recyle bin, then the 'SEG+FREE' number will be greater than the DBF_USERSPACE_MB number, which would seem wrong, since the total space available in the tablespace is DBF_USERSPACE_MB. However, DBF_USERSPACE_MB will be identical to SEG+FREE-RECYCLE. The "Free" number displayed in the Space Manager Explorer window is shown in the query as SPC_EXPLORER_FREE_MB.
select dbf.tablespace_name,
nvl (seg.segments_mb, 0) as segments_mb,
nvl (bin.recycle_bin_mb, 0) as recycle_bin_mb,
nvl (free.freespace_mb, 0) as freespace_mb,
nvl (free.freespace_mb, 0) + nvl (seg.segments_mb, 0) as "SEG+FREE",
nvl (free.freespace_mb, 0) + nvl (seg.segments_mb, 0)
- nvl (bin.recycle_bin_mb, 0) as "SEG+FREE-RECYCLE",
dbf.dbf_userspace_mb,
dbf.dbf_userspace_mb - nvl (seg.segments_mb, 0) as spc_explorer_free_mb from (select a.tablespace_name, sum (a.bytes) / (1024 * 1024) as freespace_mb
from dba_free_space a
group by a.tablespace_name) free,
(select b.tablespace_name, sum (b.bytes) / (1024 * 1024) as segments_mb
from dba_segments b
group by b.tablespace_name) seg,
(select d.tablespace_name,
sum (c.space * d.block_size) / (1024 * 1024) as recycle_bin_mb
from dba_recyclebin c, dba_tablespaces d
where c.ts_name = d.tablespace_name and d.contents = 'PERMANENT'
group by d.tablespace_name) bin,
(select e.tablespace_name, sum (user_bytes) / (1024 * 1024) as dbf_userspace_mb
from dba_data_files e, dba_tablespaces f
where e.tablespace_name = f.tablespace_name
and f.contents = 'PERMANENT'
group by e.tablespace_name) dbf
where dbf.tablespace_name = free.tablespace_name (+)
and dbf.tablespace_name = seg.tablespace_name (+)
and dbf.tablespace_name = bin.tablespace_name (+) order by dbf.tablespace_name ;
