臨時表空間用來管理資料庫排序操作以及用于存儲臨時表、中間排序結果等臨時對象,當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的介紹如下
td {border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;} table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:left;font-size:12px}
| Column | Datatype | Description |
| FILE# | NUMBER | Absolute file number |
| CREATION_CHANGE# | Creation System Change Number (SCN) | |
| CREATION_TIME | DATE | Creation time |
| TS# | Tablespace number | |
| RFILE# | 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 | Size of the file in bytes (from the file header) | |
| BLOCKS | Size of the file in blocks (from the file header) | |
| CREATE_BYTES | Creation size of the file (in bytes) | |
| BLOCK_SIZE | 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.
| NULL | |||
| FILE_NAME | Name of the database temp file | ||
| FILE_ID | File identifier number of the database temp file | ||
| TABLESPACE_NAME | VARCHAR2(30) | NOT NULL | Name of the tablespace to which the file belongs |
| Size of the file (in bytes) | |||
| Size of the file (in Oracle blocks) | |||
| CHAR(9) | File status: · · AVAILABLE | ||
| RELATIVE_FNO | Tablespace-relative file number | ||
| AUTOEXTENSIBLE | VARCHAR2(3) | Indicates whether the file is autoextensible (YES) or not (NO) | |
| MAXBYTES | maximum size of the file (in bytes) | ||
| MAXBLOCKS | Maximum size of the file (in Oracle blocks) | ||
| INCREMENT_BY | Default increment for autoextension | ||
| USER_BYTES | Size of the useful portion of the file (in bytes) | ||
| USER_BLOCKS | 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$<span style="color: #0000ff">session</span> 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; ![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)