Oracle DB: Временные сегменты в постоянных табличных пространствах

Временные сегменты в обычных табличных пространствах появляются часто, так работает база данных ORACLE.

При создании постоянного сегмента, он сначала создается как временный, а когда наполнение данными завершается, его тип изменяется на постоянный.

При удалении постоянного сегмента, его тип сначала изменяется на временный, а потом выполняется удаление из табличного пространства.

Делается так для того чтобы при сбое операции, временный сегмент автоматически вычистил системный процесс SMON.

metalink note id 181132.1

4. Temporary Segments for Permanent Segments Creation
-----------------------------------------------------
Besides sort operations, there are other SQL operations, which also require 
temporary segments: 
--> CREATE PRIMARY/UNIQUE KEY CONSTRAINT
--> ALTER TABLE ... ENABLE PRIMARY/UNIQUE CONSTRAINT
--> CREATE TABLE STORAGE (MINEXTENTS>1)
--> CREATE TABLE AS SELECT
    --> The CTAS creates a data segment in the target tablespace and marks this 
        segment as temporary in dictionary. On completion, the dictionary type 
        is changed from temporary to table. In addition, if the SELECT performs 
        a SORT operation, temporary space may be used as for a standard select.
    --> For a Parallel CTAS statement, each slave builds its own data segment 
        (marked as temporary in the dictionary) from the row source which feeds
        it. 
        Similarly, for Parallel Direct Load or Parallel Insert, each slave 
        process creates its own single temporary segment to load data into.
--> CREATE PARTITION TABLE
--> ALTER TABLE ... SPLIT PARTITION
--> CREATE SNAPSHOT
--> CREATE INDEX
    The CREATE INDEX statement, after sorting the index values, builds a 
    temporary segment in the INDEX tablespace; once the index is completely
    built, the segment type is changed to INDEX.
--> ALTER INDEX REBUILD
    During an index rebuild, besides the temporary segments used to store 
    partial sort (segments built in the user's default TEMPORARY tablespace), 
    Oracle uses a segment which is defined as a temporary segment until the 
    rebuild is complete. 
    Once this segment is fully populated, the old index can be dropped and the 
    temporary segment is redefined as a permanent segment with the index name. 
    The new version of the index, currently a temporary segment, resides in the 
    tablespace where the index is required. Note that the old index segment that
    is to be dropped is itself converted to a temporary segment first (like drop
    ping a table). Therefore, an index rebuild involves three temporary segments,
    one of which is a sort segment, that all may be located in different tablespaces.
--> DROP TABLE
    Oracle first converts the segment to a temporary segment, and starts 
    cleaning up the now temporary segments extents. If the drop is interrupted, 
    the temporary segment is cleaned up by SMON. If the SMON is interrupted by a
    shutdown abort, this may cause serious problem, and the total time to
    cleanup is increased.