From the MOS note your referenced:
Coalescing free space is not necessary for locally managed tablespaces
because bitmaps automatically track adjacent free space.
Please refer to:
Oracle9i Database Administrator's Guide
Release 2 (9.2)
I'd forgotten you had 1,024 files in your tablespace; and I'll guess that
you used parallel execution to rebuild the index - if so, what degree?
So here's one reason why you might have seen smon / PX slaves working very
hard for a while.
When you create the index in parallel every slave creates a separate
segment and when they've all finished each PX slave has a segment slave
with a list of extents which they pass to the query coordinator. The QC
picks one and makes it the final segment header, copies the lists from the
others into the one, and reformats the other segment headers. This means
you might end up with 16 * DOP extents of 64KB in one segment, then 63 *
DOP 1MB extents before you get any 8MB extents.
If the rebuild failed, or your interrupted it, then the PX segments have to
disappear. Oracle won't necessary make you wait while it clears up the
mess; it can just flag temporary segments as "to be cleared" - and that's
one of SMON's periodic jobs.
If you had a high degree of parallelism, which means a lot of TEMP
segments, and given 1,024 files, it's possible that smon would have decided
to run some of its "what segments extents do I have to sort out" in
parallel, and that may be what you saw.
I don't think Oracle will report "temporary" segments in the datafiles
(i.e. the segments allocated for creating permanent objects that will
eventually get proper names). Once upon a time you could query dba_segments
and see segments with names like 7.34265 which meant a "temporary" segment
in file 7 starting at block 34265 that needed clearing up. You might still
be able to find segments in sys.seg$ with a query like:
select type#, count(*) from seg$ where ts# = {tablespace number of your
tablespace} group by type#;
I think type 3 is 'TEMPORARY';
Regards
Jonathan Lewis
Regards
Jonathan Lewis
