臨時表空間概念
臨時表空間用來管理數據庫排序操作以及用於存儲臨時表、中間排序結果等臨時對象,當ORACLE裡需要用到SORT的時候，並且當PGA中sort_area_size大小不夠時，將會把數據放入臨時表空間裡進行排序。像數據庫中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能會用到臨時表空間。當操作完成後，系統會自動清理臨時表空間中的臨時對象，自動釋放臨時段。這裡的釋放只是標記為空閒、可以重用，其實實質占用的磁盤空間並沒有真正釋放。這也是臨時表空間有時會不斷增大的原因。
臨時表空間存儲大規模排序操作(小規模排序操作會直接在RAM裡完成，大規模排序才需要磁盤排序Disk Sort)和散列操作的中間結果.它跟永久表空間不同的地方在於它由臨時數據文件（temporary files）組成的,而不是永久數據文件（datafiles）。臨時表空間不會存儲永久類型的對象，所以它不會也不需要備份。另外，對臨時數據文件的操作不產生redo日志，不過會生成undo日志。
創建臨時表空間或臨時表空間添加臨時數據文件時，即使臨時數據文件很大，添加過程也相當快。這是因為ORACLE的臨時數據文件是一類特殊的數據文件:稀疏文件（Sparse File）,當臨時表空間文件創建時，它只會寫入文件頭部和最後塊信息(only writes to the header and last block of the file)。它的空間是延後分配的.這就是你創建臨時表空間或給臨時表空間添加數據文件飛快的原因。
另外，臨時表空間是NOLOGGING模式以及它不保存永久類型對象，因此即使數據庫損毀，做Recovery也不需要恢復Temporary Tablespace。
臨時表空間信息
查看實例的臨時表空間
SQL1：
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------
DEFAULT_TEMP_TABLESPACE TEMP
SQL2：
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;
查看臨時表空間信息：
SET LINESIZE 1200
COL NAME FOR A60
SELECT FILE# AS FILE_NUMBER
,NAME AS NAME
,CREATION_TIME AS CREATION_TIME
,BLOCK_SIZE AS BLOCK_SIZE
,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
,CREATE_BYTES/1024/1024/1024 AS "INIT_SIZE(G)"
,STATUS AS STATUS
,ENABLED AS ENABLED
FROM V$TEMPFILE;
官方文檔關於V$TEMPFILE的介紹如下
Column
Datatype
Description
FILE#
NUMBER
Absolute file number
CREATION_CHANGE#
NUMBER
Creation System Change Number (SCN)
CREATION_TIME
DATE
Creation time
TS#
NUMBER
Tablespace number
RFILE#
NUMBER
Relative file number in the tablespace
STATUS
VARCHAR2(7)
Status of the file (OFFLINE|ONLINE)
ENABLED
VARCHAR2(10)
Enabled for read and/or write
BYTES
NUMBER
Size of the file in bytes (from the file header)
BLOCKS
NUMBER
Size of the file in blocks (from the file header)
CREATE_BYTES
NUMBER
Creation size of the file (in bytes)
BLOCK_SIZE
NUMBER
Block size for the file
NAME
VARCHAR2(513)
Name of the file
SET LINESIZE 1200
COL TABLESPACE_NAME FOR A30
COL FILE_NAME FOR A60
SELECT TABLESPACE_NAME AS TABLESPACE_NAME
,FILE_NAME AS FILE_NAME
,BLOCKS AS BLOCKS
,STATUS AS STATUS
,AUTOEXTENSIBLE AS AUTOEXTENSIBLE
,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024,
MAXBYTES/1024/1024/1024)
AS "MAX_SIZE(G)"
,INCREMENT_BY AS "INCREMENT_BY"
,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE"
FROM DBA_TEMP_FILES;
DBA_TEMP_FILES describes all temporary files (tempfiles) in the database.
Column
Datatype
NULL
Description
FILE_NAME
VARCHAR2(513)
Name of the database temp file
FILE_ID
NUMBER
File identifier number of the database temp file
TABLESPACE_NAME
VARCHAR2(30)
NOT NULL
Name of the tablespace to which the file belongs
BYTES
NUMBER
Size of the file (in bytes)
BLOCKS
NUMBER
Size of the file (in Oracle blocks)
STATUS
CHAR(9)
File status:
·
· AVAILABLE
RELATIVE_FNO
NUMBER
Tablespace-relative file number
AUTOEXTENSIBLE
VARCHAR2(3)
Indicates whether the file is autoextensible (YES) or not (NO)
MAXBYTES
NUMBER
maximum size of the file (in bytes)
MAXBLOCKS
NUMBER
Maximum size of the file (in Oracle blocks)
INCREMENT_BY
NUMBER
Default increment for autoextension
USER_BYTES
NUMBER
Size of the useful portion of the file (in bytes)
USER_BLOCKS
NUMBER
Size of the useful portion of the file (in Oracle blocks)
SQL> SELECT BYTES,BLOCKS, USER_BYTES, USER_BLOCKS,
BLOCKS -USER_BLOCKS AS SYSTEM_USED
FROM DBA_TEMP_FILES;
BYTES BLOCKS USER_BYTES USER_BLOCKS SYSTEM_USED
---------- ---------- ---------- ----------- -----------
2147483648 262144 2146435072 262016 128
1073741824 131072 1072693248 130944 128
209715200 25600 208666624 25472 128
這四列中， BYTES , BLOCKS 顯示的是臨時文件有多少BYTE大小，包含多少個數據塊。而USER_BYTES,USER_BLOCKS是可用的BYTE和數據塊個數。因此，我們可以知道臨時文件中有一部分是被系統占用的，大概可以理解成文件頭信息，這一部分大小是128個block，如下圖所示:
管理臨時表空間
創建臨時表空間
下面是一個簡單的創建臨時表空間的例子，具體很多細節可以參考官方文檔，這裡省略,不做過多介紹。
http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_75a.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11366
CREATE TEMPORARY TABLESPACE TMP
TEMPFILE '/u01/gsp/oradata/TMP01.dbf'
SIZE 8G
AUTOEXTEND OFF;
增加數據文件
當臨時表空間太小時，就需要擴展臨時表空間（添加數據文件、增大數據文件、設置文件自動擴展）；有時候需要將臨時數據文件分布到不同的磁盤分區中，提升IO性能，也需要通過刪除、增加臨時表空間數據文件。
SQL> ALTER TABLESPACE TEMP
2 ADD TEMPFILE '/u04/gsp/oradata/temp02.dbf'
3 SIZE 4G
4 AUTOEXTEND ON
5 NEXT 128M
6 MAXSIZE 6G;
Tablespace altered.
SQL> ALTER TABLESPACE TMP
ADD TEMPFILE '/u03/eps/oradata/temp02.dbf'
SIZE 64G
AUTOEXTEND OFF;
Tablespace altered.
刪除數據文件
例如，我想刪除臨時表空間下的某個文件，那麼我們有兩種方式刪除臨時表空間的數據文件。
方法1：
SQL> ALTER TABLESPACE TEMP
DROP TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf';
Tablespace altered.
注意：這種刪除臨時表空間的寫法會將對應的物理文件刪除。
方法2：
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf'
DROP INCLUDING DATAFILES;
Database altered.
注意：刪除臨時表空間的臨時數據文件時，不需要指定INCLUDING DATAFILES 選項也會真正刪除物理文件，否則需要手工刪除物理文件。
調整文件大小
如下例子，需要將臨時數據文件從1G大小調整為2G
SQL> ALTER DATABASE TEMPFILE
'/u01/app/oracle/oradata/GSP/temp02.dbf' RESIZE 2G;
文件脫機聯機
SQL> ALTER DATABASE TEMPFILE
2 '/u01/app/oracle/oradata/GSP/temp02.dbf' OFFLINE;
Database altered.
SQL> ALTER DATABASE TEMPFILE
2 '/u01/app/oracle/oradata/GSP/temp02.dbf' ONLINE;
Database altered.
默認臨時表空間並不能脫機,否則會報錯，如下所示
SQL> ALTER TABLESPACE TEMP OFFLINE;
ALTER TABLESPACE TEMP OFFLINE
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
設置文件自動擴展
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp03.dbf'
2 AUTOEXTEND ON
3 NEXT 100M
4 MAXSIZE UNLIMITED;
移動重命名文件
例如，我需要將/u01/app/oracle/oradata/GSP/temp4.dbf這個文件重命名為/u01/app/oracle/oradata/GSP/temp04.dbf
1: 將臨時表空間的臨時文件脫機
SQL> ALTER DATABASE TEMPFILE
2 '/u01/app/oracle/oradata/GSP/temp4.dbf' OFFLINE;
2：移動或重命名相關的臨時文件
mv /u01/app/oracle/oradata/GSP/temp4.dbf /u01/app/oracle/oradata/GSP/temp04.dbf'
3: 使用腳本ALTER DATABASE RENAME FILE
SQL> ALTER DATABASE RENAME FILE
2 '/u01/app/oracle/oradata/GSP/temp4.dbf' TO
3 '/u01/app/oracle/oradata/GSP/temp04.dbf';
4: 將臨時表空間的臨時文件聯機
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp04.dbf' ONLINE;
Database altered.
刪除臨時表空間
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
注意：不能刪除當前用戶的默認表空間，否則會報ORA-12906錯誤
SQL> DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
如果需要刪除某一個默認的臨時表空間，則必須先創建一個臨時表空間，然後指定新創建的表空間為默認表空間，然後刪除原來的臨時表空間
臨時表空間組
臨進表空間組:
臨進表空間組是ORACLE 10g引入的一個新特性，它是一個邏輯概念，不需要顯示的創建和刪除。只要把一個臨時表空間分配到一個組中，臨時表空間組就自動創建，所有的臨時表空間從臨時表空間組中移除就自動刪除。
一個臨時表空間組必須由至少一個臨時表空間組成，並且無明確的最大數量限制.
A temporary tablespace group contains at least one tablespace. There is no limit for a group to have a maximum number of tablespaces
如果刪除一個臨時表空間組的所有成員，該組也自動被刪除。
臨時表空間的名字不能與臨時表空間組的名字相同。
It shares the namespace of tablespaces, thus its name cannot be the same as that of any tablespace.
可以在創建臨時表空間是指定表空間組，即隱式創建。
SQL>CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/u01/app/oracle/oradata/GSP/temp2_1.dbf' SIZE 200M
TABLESPACE GROUP GRP_TEMP;
查看臨時表空間組：
SQL> SELECT * FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GRP_TEMP TEMP2
也可以指定已經創建好的臨時表空間的臨時表空間組。
SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP GRP_TEMP;
Tablespace altered.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GRP_TEMP TEMP
GRP_TEMP TEMP2
從組中移除：
SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP '';
當為數據庫指定臨時表空間或為用戶指定臨時表空間時，可以使用臨時表空間組的名稱
ALTER USER DM TEMPORARY TABLESPACE GRP_TEMP;
切換臨時表空間
1：查看舊臨時表空間信息
SELECT * FROM V$TEMPFILE
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS
2：創建中轉的臨時表空間
3：添加相應的數據文件
4：切換臨時表空間。
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;
5：刪除舊的臨時表空間數據文件
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
6：如果有必要，重新指定用戶臨時表空間為新建的臨時表空間
ALTER USER ODS TEMPORARY TABLESPACE TMP;
ALTER USER EDS TEMPORARY TABLESPACE TMP;
ALTER USER ETL TEMPORARY TABLESPACE TMP;
ALTER USER DM TEMPORARY TABLESPACE TMP;
收縮臨時表空間
排序等操作使用的臨時段，使用完成後會被標記為空閒，表示可以重用，占用的空間不會立即釋放，有時候臨時表空間會變得非常大，此時可以通過收縮臨時表空間來釋放沒有使用的空間。收縮臨時表空間是ORACLE 11g新增的功能。
SQL> ALTER TABLESPACE TEMP SHRINK SPACE KEEP 8G;
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf'
監控臨時表空間
查看臨時表空間使用情況：
SELECT TU.TABLESPACE_NAME AS "TABLESPACE_NAME",
TT.TOTAL - TU.USED AS "FREE(G)",
TT.TOTAL AS "TOTAL(G)",
ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3) AS "USED(%)",
ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
FROM GV_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) TU ,
(SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;
COL TEMP_FILE FOR A60;
SELECT ROUND((F.BYTES_FREE + F.BYTES_USED)/1024/1024/1024, 2) AS "TOTAL(GB)",
ROUND(((F.BYTES_FREE + F.BYTES_USED) - NVL(P.BYTES_USED, 0))/1024/1024/1024,2) AS "FREE(GB)",
D.FILE_NAME AS "TEMP_FILE",
ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)" ,
ROUND((F.BYTES_USED + F.BYTES_FREE)/1024/1024/1024, 2) AS "TOTAL(GB)",
ROUND(((F.BYTES_USED + F.BYTES_FREE) - NVL(P.BYTES_USED, 0))/1024/1024/1024, 2) AS "FREE(GB)" ,
ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)"
FROM SYS.V_$TEMP_SPACE_HEADER F ,DBA_TEMP_FILES D ,SYS.V_$TEMP_EXTENT_POOL P
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
AND F.FILE_ID(+) = D.FILE_ID
AND P.FILE_ID(+) = D.FILE_ID;
查看臨時表空間對應的臨時文件的使用情況
SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,
BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED ,
BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREE
FROM V$TEMP_SPACE_HEADER
ORDER BY 1 DESC;
查找消耗臨時表空間資源比較多的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;
參考資料：
http://www.orafaq.com/node/2
http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11359
昨天在做測試的時候發現一個非常奇怪的問題：在程序的查詢模塊中做查詢的時候，開始速度很快，但是過了一段時間以後速度就變慢，最後干脆就報錯，不工作了。在排錯的過程中，發現Oracle臨時表空間暴漲，達到了幾十個GB，在Oracle中對Session進行跟蹤，發現磁盤空間還在不停的消耗，幾乎是每隔5s，臨時表空間就會增長500MB左右，最後報錯的原因應該是因為沒有磁盤空間可以分配造成的。這是一件十分恐怖的事情。 我們知道Oracle臨時表空間主要是用來做查詢和存放一些緩存的數據的，磁盤消耗的一個主要原因是需要對查詢的結果進行排序，如果沒有猜錯的話，在磁盤空間的（內存）的分配上，Oracle使用的是貪心算法，如果上次磁盤空間消耗達到1GB，那麼臨時表空間就是1GB，如果還有增長，那麼依此類推，臨時表空間始終保持在一個最大的上限。像上文提到的恐怖現象經過分析可能是以下幾個方面的原因造成的。 1. 沒有為臨時表空間設置上限，而是允許無限增長。但是如果設置了一個上限，最後可能還是會面臨因為空間不夠而出錯的問題，臨時表空間設置太小會影響性能，臨時表空間過大同樣會影響性能，至於需要設置為多大需要仔細的測試。 2.查詢的時候連表查詢中使用的表過多造成的。我們知道在連表查詢的時候，根據查詢的字段和表的個數會生成一個迪斯卡爾積，這個迪斯卡爾積的大小就是一次查詢需要的臨時空間的大小，如果查詢的字段過多和數據過大，那麼就會消耗非常大的臨時表空間。 3.對查詢的某些字段沒有建立索引。Oracle中，如果表沒有索引，那麼會將所有的數據都復制到臨時表空間，而如果有索引的話，一般只是將索引的數據復制到臨時表空間中。 針對以上的分析，對查詢的語句和索引進行了優化，情況得到緩解，但是需要進一步測試。 總結： 1.SQL語句是會影響到磁盤的消耗的，不當的語句會造成磁盤暴漲。 2.對查詢語句需要仔細的規劃，不要想當然的去定義一個查詢語句，特別是在可以提供用戶自定義查詢的軟件中。 3.仔細規劃表索引。臨時表空間使用注意:1.臨時表空間 是用於在進行排序操作(如大型查詢,創建索引和聯合查詢期間存儲臨時數據)每個用戶都有一個臨時表空間2.對於大型操作頻繁,(大型查詢,大型分類查詢,大型統計分析等),應指定單獨的臨時表空間,以方便管理3.分配用戶單獨臨時表空間,一般是針對 大型產品數據庫,OLTP數據庫,數據庫倉庫對於小型產品不需要單獨制定臨時表空間,使用默認臨時表空間
首先查看temp是不是某些用戶的默認表空間
SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;
如果有用戶的默認臨時表空間是temp的話，建議進行更改
alter user xxx temporary tablespace tempdefault;
設置tempdefault為默認臨時表空間
alter database default temporary tablespace tempdefault;
然後用你列出來的語句刪除即可
drop tablespace temp including contents and datafile;
即便丟失了所有的臨時表空間，只要不是數據庫當中用到了order by、子查詢、group by、distinct等需要消耗臨時表空間的語句（而且要比較大才行，小的話就直接用pga的SORT_AREA區了），那麼也不會對業務造成錯誤導致中斷，發現問題之後只需要新建一個臨時表空間就可以了。你要是了解備份恢復的話，實際上在進行備份的時候臨時表空間都不會進行備份，而只是有一個創建臨時表空間的語句而已。
