Temporary tablespace是oracle裡臨時表空間臨時表空間主要用途是在數據庫進行排序運算管理索引訪問視圖等操作時提供臨時的運算空間當運算完成之後系統會自動清理當oracle裡需要用到sort的時候PGA中sort_area_size大小不夠時將會把數據放入臨時表空間裡進行排序同時如果有異常情況的話也會被放入臨時表空間正常來說在完成Select語句create index等一些使用TEMP表空間的排序操作後Oracle是會自動釋放掉臨時段的但有些有侯我們則會遇到臨時段沒有被釋放TEMP表空間幾乎滿的狀況甚至是我們重啟了數據庫仍沒有解決問題
排序是很耗資源的Temp表空間滿了關鍵是優化你的語句盡量使排序減少才是上策
Temp表空間滿時的處理方法
一修改參數(僅適用於i及i以下版本)
修改一下TEMP表空間的storage參數讓Smon進程觀注一下臨時段從而達到清理和TEMP表空間的目的
SQL>alter tablespace temp increase ;
SQL>alter tablespace temp increase ;
二kill session
使用如下語句a查看一下認誰在用臨時段
SELECT seusername seSID seserial# sesql_address semachine seprogram suTABLESPACE
susegtype suCONTENTS FROM v$session se v$sort_usage su
WHERE sesaddr = susession_addr
kill正在使用臨時段的進程
SQL>Alter system kill session sidserial#;
把TEMP表空間回縮一下
SQL>Alter tablespace TEMP coalesce;
注
這處方法只能針對字典管理表空間(Dictionary Managed Tablespace)於本地管理表空間（LMT:Local Managed Tablespace）不需要整理的i以後只能創建本地管理的表空間
CREATE TABLESPACE TEST DATAFILE D:\TESTdbf SIZE M EXTENT MANAGEMENT DICTIONARY
CREATE TABLESPACE TEST DATAFILE D:\TESTdbf SIZE M EXTENT MANAGEMENT LOCAL;
三重啟數據庫庫
庫重啟時Smon進程會完成臨時段釋放TEMP表空間的清理操作不過很多的時侯我們的庫是不允許down的所以這種方法缺少了一點的應用機會不過這種方法還是很好用的
四使用診斷事件的一種方法也是最有效的一種方法
確定TEMP表空間的ts#
SQL>select ts# name from systs$ ;
TS# NAME
SYSTEM
UNDOTBS
SYSAUX
TEMP
USERS
UNDOTBS
執行清理操作
SQL>alter session set events immediate trace name DROP_SEGMENTS level ;
說明
temp表空間的TS# 為 So TS#+ =
重建TEMP 表空間
Temporary tablespace是不能直接drop默認的臨時表空間的不過我們可以通過以下方法來做
准備查看目前的Temporary Tablespace
SQL> select name from v$tempfile;
NAME
———————————————————————
D:\ORACLE\ORADATA\TEST\TEMPDBF
SQL> select usernametemporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
創建中轉臨時表空間
create temporary tablespace TEMP TEMPFILE E:\ORACLE\ORADATA\ORCL\tempDBF SIZE M REUSE AUTOEXTEND ON NEXT M MAXSIZE UNLIMITED;
改變缺省臨時表空間 為剛剛創建的新臨時表空間temp
alter database default temporary tablespace temp;
刪除原來臨時表空間
drop tablespace temp including contents and datafiles;
重新創建臨時表空間
create temporary tablespace TEMP TEMPFILE E:\ORACLE\ORADATA\ORCL\tempDBF SIZE M REUSE AUTOEXTEND ON NEXT M MAXSIZE UNLIMITED;
重置缺省臨時表空間為新建的temp表空間
alter database default temporary tablespace temp;
刪除中轉用臨時表空間
drop tablespace temp including contents and datafiles;
如果有必要那麼重新指定用戶表空間為重建的臨時表空間
alter user arbor temporary tablespace temp;
查看表空間語句不過查不出Temp表空間
SELECT UPPER(FTABLESPACE_NAME) 表空間名
DTOT_GROOTTE_MB 表空間大小(M)
DTOT_GROOTTE_MB FTOTAL_BYTES 已使用空間(M)
TO_CHAR(ROUND((DTOT_GROOTTE_MB FTOTAL_BYTES) / DTOT_GROOTTE_MB * )
) 使用比
FTOTAL_BYTES 空閒空間(M)
FMAX_BYTES 最大塊(M)
FROM (SELECT TABLESPACE_NAME
ROUND(SUM(BYTES) / ( * ) ) TOTAL_BYTES
ROUND(MAX(BYTES) / ( * ) ) MAX_BYTES
FROM SYSDBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
(SELECT DDTABLESPACE_NAME
ROUND(SUM(DDBYTES) / ( * ) ) TOT_GROOTTE_MB
FROM SYSDBA_DATA_FILES DD
GROUP BY DDTABLESPACE_NAME) D
WHERE DTABLESPACE_NAME = FTABLESPACE_NAME
ORDER BY DESC
From:http://tw.wingwit.com/Article/program/Oracle/201311/17017.html
