Oracle DB: Временные сегменты в постоянных табличных пространствах
Версия от 13:37, 30 января 2023; Admin (обсуждение | вклад) (Admin переименовал страницу Oracle DB: Временные сегменты в обычных табличных пространствах в 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.