Temporary資料表空間100%解決方案

在完成Select語句、create index等一些使用TEMP資料表空間的排序操作後，Oracle是會自動釋放臨時段。但有些有侯我們則會遇到臨時段沒有被釋放，TEMP資料表空間幾乎滿的狀況，甚至是我們重啟了資料庫仍沒有解決問題。下面總結出幾種處理方法：
重啟庫
資料庫重啟時，Smon進程會完成臨時段釋放,TEMP資料表空間的清理操作，不過很多的時侯我們的庫是不允許down的,所以這種方法缺少了一點的應用機會，不過這種方法還是很好用的。

修改TEMP資料表空間的storage參數
這個方法來自Metalink主要是讓Smon進程觀注一下臨時段，從而達到清理和TEMP資料表空間的目的。
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;
以上方法在9I和10G不行，
SQL> alter tablespace temp PCTINCREASE 1;
alter tablespace temp PCTINCREASE 1
*
第 1 行出現錯誤:
ORA-02142: ALTER TABLESPACE 選項缺失或無效
殺死會話，回收收縮

句a查看一下認誰在用臨時段
SQL>SELECT username,
  sid,
  serial#,
  sql_address,
  machine,
  program,
  tablespace,
  segtype,
  contents
FROM v$session se,
  v$sort_usage su
WHERE se.saddr=su.session_addr
殺死正在使用臨時段的進程
SQL>Alter system kill session 'sid,serial#';
回縮TEMP資料表空間
SQL>Alter tablespace TEMP coalesce;
使用診斷事件
確定TEMP資料表空間的ts#
SQL>select ts#, name from sys.ts$ ;
TS# NAME
-----------------------
0 SYSYEM
1 RBS
2 USERS
3* TEMP
4 TOOLS
5 INDX
6 DRSYS
執行清理操作
SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4' ;
說明：
temp資料表空間的TS# 為 3*, So TS#+ 1= 4
一下是摘自Metalink的說明
The DROP_SEGMENTS event
~~~~~~~~~~~~~~~~~~~~~~~
 Available from 8.0 onwards.
 
   DESCRIPTION
     Finds all the temporary segments in a tablespace which are not
     currently locked and drops them.
     For the purpose of this event a "temp" segment is defined as a
     segment (seg$ entry) with TYPE#=3. Sort space in a TEMPORARY
     tablespace does not qualify under this definition as such
     space is managed independently of SEG$ entries.

   PARAMETERS
     level - tablespace number+1. If the value is 2147483647 then
             temp segments in ALL tablespaces are dropped, otherwise, only
             segments in a tablespace whose number is equal to the LEVEL
             specification are dropped.

   NOTES
     This routine does what SMON does in the background, i.e. drops
     temporary segments. It is provided as a manual intervention tool which
     the user may invoke if SMON misses the post and does not get to
     clean the temp segments for another 2 hours. We do not know whether
     missed post is a real possibility or more theoretical situation, so
     we provide this event as an insurance against SMON misbehaviour.

     Under normal operation there is no need to use this event.

     It may be a good idea to
        alter tablespace <tablespace> coalesce;
     after dropping lots of extents to tidy things up.

 *SQL Session (if you can connect to the database):     
    alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';

     The TS# can be obtained from v$tablespace view:
     select ts# from v$tablespace where name = '<Tablespace name>';

     Or from SYS.TS$:

     select ts# from sys.ts$ where name = '<Tablespace name>' and online$ != 3;
    
     If ts# is 5, an example of dropping the temporary segments in that tablespace
     would be:

    alter session set events 'immediate trace name DROP_SEGMENTS level 6';
暫存資料表空間相關常用sql

查看暫存資料表空間的使用方式（GV_$TEMP_SPACE_HEADER視圖必須在sys使用者下才能查詢）
GV_$TEMP_SPACE_HEADER視圖記錄了暫存資料表空間的使用大小與未使用的大小
dba_temp_files視圖的bytes欄位記錄的是暫存資料表空間的總大小
SELECT temp_used.tablespace_name,
       total - used as "Free",
       total as "Total",
       round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
          FROM GV_$TEMP_SPACE_HEADER
         GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_temp_files
         GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name

尋找比較消耗暫存資料表空間資源的sql語句
Select se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as Space,
       tablespace,
       segtype,
       sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
 order by se.username, se.sid

查看當前暫存資料表空間使用大小與正在佔用暫存資料表空間的sql語句
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  from v$sort_usage sort, v$session sess, v$sql sql
 where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sess.SQL_ADDRESS
 order by blocks desc;
個人認為，暫存資料表空間組可以有效減少暫存資料表空間使用率100%的情況

具體可參見