临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。注意这里的释放，仅仅是将这些空间标记为空闲，并可重用，真正占用的磁盘空间并没有释放。所以Temp表空间可能会越来越大。
排序是很耗资源的，Temp表空间满了,关键是优化你的语句，尽量使排序减少才是上策.
创建临时表空间
view plain
SQL> CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '+dgroup1'
2 SIZE 20M REUSE
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
表空间已创建。
SQL> select t.file_name from dba_temp_files t;
FILE_NAME
--------------------------------------------------------------------------------
+DGROUP1/oralife/tempfile/temp.265.762803345
+DGROUP1/oralife/tempfile/lmtemp.290.769965543
The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The Oracle Database default for SIZE is 1M. But if you want to specify another value for SIZE, you can do so as shown in the preceding statement.
自动分配不允许应用于临时表空间。
UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes.The default SIZE is 1 megabyte. All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM in order to specify SIZE. You cannot specify UNIFORM for an undo tablespace.
Temp 表空间必须是uniform 的，undo 必须是autoallocate的。默认情况下uniform 是1M。
If you do not specify AUTOALLOCATE or UNIFORM, then the default is UNIFORM for temporary tablespaces and AUTOALLOCATE for all other types of tablespaces.
使用ALTER TABLESPACE添加临时文件,将临时文件offline,online
view plain
SQL> ALTER TABLESPACE lmtemp
2 ADD TEMPFILE '+dgroup1' SIZE 18M REUSE;
Tablespace altered
SQL> select t.file_name from dba_temp_files t;
FILE_NAME
--------------------------------------------------------------------------------
+DGROUP1/oralife/tempfile/temp.265.762803345
+DGROUP1/oralife/tempfile/lmtemp.290.769965543
+DGROUP1/oralife/tempfile/lmtemp.271.769965767
SQL> ALTER TABLESPACE LMTEMP TEMPFILE ONLINE;
Tablespace altered
SQL> SELECT t2.NAME,t2.STATUS FROM v$tempfile t2;
NAME STATUS
-------------------------------------------------------------------------------- -------
+DGROUP1/oralife/tempfile/temp.265.762803345 ONLINE
+DGROUP1/oralife/tempfile/lmtemp.290.769965543 ONLINE
+DGROUP1/oralife/tempfile/lmtemp.271.769965767 ONLINE
SQL> ALTER TABLESPACE LMTEMP TEMPFILE OFFLINE;
Tablespace altered
SQL> SELECT t2.NAME,t2.STATUS FROM v$tempfile t2;
NAME STATUS
-------------------------------------------------------------------------------- -------
+DGROUP1/oralife/tempfile/temp.265.762803345 ONLINE
+DGROUP1/oralife/tempfile/lmtemp.290.769965543 OFFLINE
+DGROUP1/oralife/tempfile/lmtemp.271.769965767