临时表空间概念
临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当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。
1、查看临时表空间信息：
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE2 FROM DATABASE_PROPERTIES3 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';PROPERTY_NAME PROPERTY_VALUE------------------------------ ---------------------------------DEFAULT_TEMP_TABLESPACE TEMP查看用户的默认表空间：SQL> select username,temporary_tablespace from dba_users;USERNAME TEMPORARY_TABLESPACE------------------------------ ------------------------------MGMT_VIEW TEMPSYS TEMPSYSTEM TEMPDBSNMP TEMPSYSMAN TEMPSCOTT TEMPHR TEMPOUTLN TEMPMDSYS TEMPORDSYS TEMPEXFSYS TEMPDMSYS TEMPWMSYS TEMPCTXSYS TEMPANONYMOUS TEMPXDB TEMPORDPLUGINS TEMPSI_INFORMTN_SCHEMA TEMPOLAPSYS TEMPTSMSYS TEMPUSERNAME TEMPORARY_TABLESPACE------------------------------ ------------------------------BI TEMPPM TEMPMDDATA TEMPIX TEMPSH TEMPDIP TEMPOE TEMP27 rows selected
查看临时表空间对应的数据文件信息：
SQL> SELECT FILE# AS FILE_NUMBER 2 ,NAME AS NAME 3 ,CREATION_TIME AS CREATION_TIME 4 ,BLOCK_SIZE AS BLOCK_SIZE 5 ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" 6 ,CREATE_BYTES/1024/1024/1024 AS "INIT_SIZE(G)" 7 ,STATUS AS STATUS 8 ,ENABLED AS ENABLED 9 FROM V$TEMPFILE;FILE_NUMBER NAME CREATION_TIME BLOCK_SIZE FILE_SIZE(G) INIT_SIZE(G) STATUS ENABLED----------- ------------------------------------------------------------ -------------- ---------- ------------ ------------ ------- ---------- 1 I:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF 10-2月 -15 8192 .028320313 .01953125 ONLINE READ WRITE 2 I:\TESTDATA\LXJ_TEMP.DBF 10-2月 -15 8192 .09765625 .09765625 ONLINE READ WRITE
增加删除临时表空间的数据文件
当临时表空间太小时，就需要扩展临时表空间（添加数据文件、增大数据文件、设置文件自动扩展）；有时候需要将临时数据文件分布到不同的磁盘分区中，提升IO性能，也需要通过删除、增加临时表空间数据文件。
SQL> alter tablespace temp --增加临时表空间数据文件 2 add tempfile '/home/oracle/oradata/orcl/temp02.dbf' 3 size 1G 4 autoextend on 5 next 50M 6 maxsize 2G;Tablespace alteredSQL> alter tablespace temp --增加临时表空间数据文件 2 add tempfile '/home/oracle/oradata/orcl/temp03.dbf' 3 size 10M 4 autoextend off;Tablespace alteredSQL> alter tablespace temp --同时删除数据文件 2 drop tempfile '/home/oracle/oradata/orcl/temp03.dbf';Tablespace alteredSQL> alter database tempfile '/home/oracle/oradata/orcl/temp02.dbf' --同时删除数据文件 2 drop including datafiles;Database alteredSQL>
临时表空间数据文件联机/脱机
SQL> alter database tempfile '/home/oracle/oradata/orcl/temp01.dbf' offline;Database alteredSQL> alter database tempfile '/home/oracle/oradata/orcl/temp01.dbf' online;Database altered
调整临时表空间数据文件大小
SQL> alter database tempfile '/home/oracle/oradata/orcl/temp01.dbf' resize 5G;
Database altered-------
默认临时表空间并不能脱机,否则会报错，如下所示
SQL> alter tablespace temp offline;
alter tablespace temp offlineORA-03217: invalid option for alter of TEMPORARY TABLESPACE
设置文件自动扩展：
SQL> alter database tempfile '/home/oracle/oradata/orcl/temp01.dbf'2 autoextend on3 next 100M 4 maxsize unlimited;Database altered
移动重命名文件
例如，我需要将
/home/oracle/oradata/orcl/temp01.dbf 这个文件重命名为/home/oracle/oradata/orcl/temp02.dbf
1: 将临时表空间的临时文件脱机
SQL> ALTER DATABASE TEMPFILE '/home/oracle/oradata/orcl/temp01.dbf' OFFLINE;
2：移动或重命名相关的临时文件
mv /home/oracle/oradata/orcl/temp01.dbf /home/oracle/oradata/orcl/temp01.dbf
3: 使用脚本ALTER DATABASE RENAME FILE
SQL> ALTER DATABASE RENAME FILE '/home/oracle/oradata/orcl/temp01.dbf' TO '/u01/app/oracle/oradata/GSP/temp02.dbf';
4: 将临时表空间的临时文件联机
SQL> ALTER DATABASE TEMPFILE '/home/oracle/oradata/orcl/temp02.dbf' ONLINE;
Database altered.
删除临时表空间
SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
注意：不能删除当前用户的默认表空间，否则会报ORA-12906错误。如果需要删除某一个默认的临时表空间，则必须先创建一个临时表空间，然后指定新创建的表空间为默认表空间，然后删除原来的临时表空间。
收缩临时表空间
排序等操作使用的临时段，使用完成后会被标记为空闲，表示可以重用，占用的空间不会立即释放，有时候临时表空间会变得非常大，此时可以通过收缩临时表空间来释放没有使用的空间。收缩临时表空间是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) TTWHERE 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 PWHERE 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_FREEFROM V$TEMP_SPACE_HEADERORDER BY 1 DESC;
查找消耗临时表空间资源比较多的SQL语句
SELECT se.username, se.sid, su.extents, su.blocks * to_number(rtrim(p.value)) as Space, tablespace, segtype, sql_textFROM 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.sqladdrORDER BY se.username, se.sid;
《FROM:》
在EM界面查看临时表空间，空间占用率一般在99%或是100%，并不是指临时表空间分配太小。
查看临时表空间实际占用情况：
SELECT BLOCKS*块大小 FROM V$SORT_USAGE;