In item 29 of Health Check what does # of holes mean? (4301802)

In item 29 of Health Check what does # of holes mean?

Here's the report produced:

Tablespace Fragmentation (1)
A common set of extent sizes is a good way to reduce tablespace fragmentation.
Sometimes fragmentation can be improved with 'Alter tablespace <tblspace> coalesce'.
Tablespace fragmentation should not be a problem if you use locally managed tablespaces.
======================================================================
== Tablespace Name % # of # of ==
== Fragmented Extents Holes ==
======================================================================
USERS 15 990 175

# of holes is the number of entries in DBA_FREESPACE for a particluar tablespace. This is the SQL Toad fires off to get this information:

select s.tablespace_name,
round(100 * f.hole_count / (f.hole_count + s.seg_count)) pct_fragmented,
s.seg_count segments, f.hole_count holes
from
(Select tablespace_name, count(*) seg_count
from dba_segments
group by tablespace_name) s,
(Select tablespace_name, count(*) hole_count
from dba_free_space
group by tablespace_name) f
where s.tablespace_name = f.tablespace_name
and s.tablespace_name in (Select tablespace_name from dba_tablespaces where contents = 'PERMANENT')
and 100 * f.hole_count / (f.hole_count + s.seg_count) > 2
and s.seg_count > 50

So 'Holes' is derived from the number of entries in DBA_FREE_SPACE for a particular tablespace i.e.

Select tablespace_name, count(*) hole_count
from dba_free_space
group by tablespace_name

The fragmentation is derived as above i.e.

round(100 * f.hole_count / (f.hole_count + s.seg_count)) pct_fragmented