It is a half-truth. SMON executes the following query every 5 minutes.
select
f.file#,
f.block#,
f.ts#,
f.length
from
fet$ f,
ts$ t
where
t.ts# = f.ts# and
t.dflextpct != 0 and
t.bitmapped = 0
The query returns one row for each free extent in all the candidate tablespaces
for coalescing. SMON then sorts the result set and scans through for sets of
contiguous free extents that can be coalesced. Up to 8 such sets of extents may
be coalesced each time this is done. Because the free extent rows are first
sorted, low numbered tablespaces will always be coalesced before higher number
ones. However, there is no limit on the number of tablespaces that will be
considered.
So the "trick" works as intended. But that does not mean that I like it. In fact, I dislike it. A tablespace level uniform extent size policy is more reliable and more efficient. See the Ixora web tip Planning Extents for my reasoning.
Today I've been told by two very experienced DBAs, that the trick
"PCTINCREASE for the tablespace to 1; PCTINCREASE for each segment to 0; then SMON will regularly coalesce freed extends" doesn't work, because
a) SMON
starts his work at the SYSTEM tablespace, until OS gives the CPU to another
process, when SMON gets back the CPU, he will again begin with the
SYSTEM-tablespace, and
b) SMON tries this only 8 times, then never more.
Conclusion: set PCTINCREASE to 0 for the tablespace and do not specify it
for the single segments.
Now my question: Is that right? I heard that for the first time.