經常看到有人說看到temporary tablespace空間不釋放等等的問題，整理一篇metalink上的相關文章，入門級的，還算淺顯吧。基本上就是翻譯了。
有疑問的歡迎討論。

參考metalink：Temporary Segments Are Not Being De-Allocated After a Sort（Note:1039341.6）
sort之後臨時段不回收

描述
======

當你監控temporary tablespace 的空閒空間，發現空閒空間並沒有隨著sort的結束而增加，甚至當前資料庫沒有任何客戶端連線也是如此：仍然有大量的temporary型別的extent存在於temporary tablespace，有時候使用者會在執行事務的時候遇到ora-1652（在temp表空間上），這個錯誤表明沒有足夠的空間，但是當使用者的查詢失敗後，smon並沒有去清理臨時段。

Scope & Application
===================

如果TEMPORARY TABLESPACE的型別是TEMPORARY，TEMPORARY TABLESPACE裡的使用過的空間是不會被釋放的，除非shutdown。
如果是PERMANENT，SMON會在process不再使用臨時段之後去做清理。

如果使用TEMPORARY型別的臨時表空間，資料庫剛剛startup後，第一個使用TEMPORARY tablespace進行排序的statement會創一個建sort segment，這個segment不會被釋放，除非資料庫restart，可以用V$SORT_SEGMENT察看當前的已分配了的sort segments地使用情況。


如果是用PERMANENT tablespace作排序，由smon負責在statement結束之後刪除被建立的temporary segments，這樣空間可以被其他物件使用。

使用如下查詢：
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE ,
TABLESPACE_NAME
from DBA_SEGMENTS
where SEGMENT_TYPE = 'TEMPORARY';

to give results similar to:

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
----- ------------ ------------ ---------------
SYS 4.2 TEMPORARY TEMP

Note: segment_name and tablepace_name are likely to be different.

tablespace的型別可以用如下查詢：
select TABLESPACE_NAME,
CONTENTS
from DBA_TABLESPACES
where TABLESPACE_NAME in ( );

to give results similar to:

TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY
TEMP1 PERMANENT


解釋
=====

1)
對於使用了TEMPORARY型別的TEMPORARY TABLESPACE，臨時段被使用後不被回收是正常的行為（7.3以後），這時如果有ora-1652發生，說明臨時表空間是真得不夠:-)

系統中的臨時段在oracle startup之後被建立，並只有在oracle shutdown的時候被釋放，如果存在大量的extent，一個可能的原因是你的storage子句的設定有問題。

考慮到效能原因，當一個temporary extent被分配的時候，tablespace會做一個標記，操作結束之後這個extent不會被釋放或回收，相應的，這個extent被簡單的標誌為free，對於後面的sort操作是available的，這樣就省去了系統分配和回收temporary extent的負載。


2）
對於在PERMANENT的tablespace裡面使用temporary segment（別讓概念搞昏了，呵呵），記得檢查parameter file裡面沒有設定如下引數：
event="10061 trace name context forever, level 10"
event="10269 trace name context forever, level 10"

這兩個引數禁止smon去做temporary segment的clean up和coalescing，在oraus.msg裡面可以看到具體的定義：
10061, 00000, "disable SMON from cleaning temp segments
10269, 00000, "Don't do coalesces of free space in SMON"

// *Cause: setting this event prevents SMON from doing free space coalesces

（正常情況下，smon會負責定期做temporary segment的clean up和coalescing。具體作的方式metalink可以找到）


透過查詢V$SORT_SEGMENT來看temp segment是free還是being used

For example:
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

>>> DURING the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
---------------- ------------ ----------- -----------
TEMP 590 590 0


>>> AFTER the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
--------------- ------------ ------------ -----------
TEMP 590 0 590


透過這個查詢可以看到在做sort的時候，segment標識為used，sort結束後，標誌為free。實際上這時候相應的extents都被釋放回SEP（sort extent pool）裡面。（SEP是SGA裡面存放temp extent的部分，具體描述在metalink也可以找到）


最後，把tablespace的型別改為temporary可以這樣：
ALTER TABLESPACE TEMPORARY;

如果temp被擴大 關於回收temp空間大小 9i就是shutdown在startup也不能 回收空間
只能重建才能回收空間

temporary 型別的temporary tablespace，是沒有回收這個概念，用完了的temp extent，只是標誌為free，不回收。temp表空間為autoextend,所以一直擴張.

看我翻譯的文章，當你使用的temporary tablespace 的型別是PERMANENT的時候，smon才會負責回收空間。如果你的型別時TEMPORARY，smon不會起作用，這跟表空間是否 locally managed (還是字典管理表空間)是不同概念吧。

可以將表空間的預設儲存引數pctincrease改為非0。一般將其設為1，如：
alter tablespace temp
default storage(pctincrease 1);
這樣smon便會將自由範圍自動合併。也可以手工合併自由範圍：
alter tablespace temp coalesce; (注:這個僅僅對使用字典管理的temp空間有用。)

Oracle臨時表空間為何暴漲？

昨天在做測試的時候發現一個非常奇怪的問題：在程式的查詢模組中做查詢的時候，開始速度很快，但是過了一段時間以後速度就變慢，最後乾脆就報錯，不工作了。在排錯的過程中，發現Oracle臨時表空間暴漲，達到了幾十個GB，在Oracle中對Session進行跟蹤，發現磁碟空間還在不停的消耗，幾乎是每隔5s，臨時表空間就會增長500MB左右，最後報錯的原因應該是因為沒有磁碟空間可以分配造成的。這是一件十分恐怖的事情。

我們知道Oracle臨時表空間主要是用來做查詢和存放一些快取的資料的，磁碟消耗的一個主要原因是需要對查詢的結果進行排序，如果沒有猜錯的話，在磁碟空間的（記憶體）的分配上，Oracle使用的是貪心演算法，如果上次磁碟空間消耗達到1GB，那麼臨時表空間就是1GB，如果還有增長，那麼依此類推，臨時表空間始終保持在一個最大的上限。像上文提到的恐怖現象經過分析可能是以下幾個方面的原因造成的。
1. 沒有為臨時表空間設定上限，而是允許無限增長。但是如果設定了一個上限，最後可能還是會面臨因為空間不夠而出錯的問題，臨時表空間設定太小會影響效能，臨時表空間過大同樣會影響效能，至於需要設定為多大需要仔細的測試。
2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候，根據查詢的欄位和表的個數會生成一個迪斯卡爾積，這個迪斯卡爾積的大小就是一次查詢需要的臨時空間的大小，如果查詢的欄位過多和資料過大，那麼就會消耗非常大的臨時表空間。
3.對查詢的某些欄位沒有建立索引。Oracle中，如果表沒有索引，那麼會將所有的資料都複製到臨時表空間，而如果有索引的話，一般只是將索引的資料複製到臨時表空間中。
針對以上的分析，對查詢的語句和索引進行了最佳化，情況得到緩解，但是需要進一步測試。

總結：
1.SQL語句是會影響到磁碟的消耗的，不當的語句會造成磁碟暴漲。
2.對查詢語句需要仔細的規劃，不要想當然的去定義一個查詢語句，特別是在可以提供使用者自定義查詢的軟體中。
3.仔細規劃表索引。

[@more@]