oracle数据库的临时表空间的主要用途是在数据库进行排序运算，管理索引，访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle数据库要用到排序操作sort，pga中sort_area_size大小不够时，将会把这些数据放入临时表空间进行排序同时如果有异常情况也会被放入临时表空间。正常来说，在完成select语句，creat index等一些使用temp表空间的排序操作之后，oracle会自动释放临时段的，这里说的释放，仅仅是将这些空间标记为空闲，并可重用，真正占用的磁盘空间并没有释放，所以temp表空间可能会越来越大。
通过重启数据库可以释放临时表空间，如果不能重启实例，而一直保持有问题的sql语句执行，temp表空间会一直增长，知道消耗完硬盘空间为止。
在临时表空间的磁盘分配上，oracle采用的是贪心算法，如果上次磁盘空间消耗达到1GB,那么临时表空间就是1GB，也就是说当前临时表空间的大小是历史上使用临时表空间的最大值。
临时表空间主要用于：
(1)create index或rebuild index
(2)order by 或group by
(3)distinct操作
(4)union或intersect或minus
(5)sort-merge joins
(6)analyze
排序时很耗资源的，不能无限增大temp表空间，关键是优化你的语句，尽量使排序减少才是上策。

查看当前数据库临时表空间和临时数据文件使用率：
SYS@orcl 07-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
  2  total-used as "free|MB",
  3  total as "total|MB"
  4  from 
  5  (select tablespace_name,sum(bytes_used)/1024/1024 used
  6  from gv_$temp_space_header
  7  group by tablespace_name) temp_used,
  8  (select file_name,tablespace_name,sum(bytes)/1024/1024 total
  9  from dba_temp_files
 10  group by tablespace_name,file_name) temp_total
 11  where temp_used.tablespace_name=temp_total.tablespace_name;


TABLESPACE_NAME FILE_NAME                              free|MB   total|MB
--------------- ----------------------------------- ---------- ----------
TEMP            /u01/app/oracle/oradata/orcl/temp01         12         20
                .dbf


查看当前数据库默认的临时表空间：
SYS@orcl 07-SEP-14>select property_name,property_value
  2  from database_properties
  3  where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                       PROPERTY_VALUE
----------------------------------- -----------------------------------
DEFAULT_TEMP_TABLESPACE             TEMP


改变临时表空间的大小：
SYS@orcl 07-SEP-14>alter database tempfile
  2  '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 25m;


Database altered.

SYS@orcl 07-SEP-14>select tablespace_name,file_name,bytes/1024/1024||'MB'
  2  from dba_temp_files;


TABLESPACE_NAME FILE_NAME                                          BYTES/1024/1024||'MB'
--------------- -------------------------------------------------- ------------------------------------------
TEMP            /u01/app/oracle/oradata/orcl/temp01.dbf            25MB


将临时表空间设置成自动扩展：
SYS@orcl 07-SEP-14>alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5m maxsize unlimited;

Database altered.

向临时表空间增加数据文件:
SYS@orcl 07-SEP-14>alter tablespace temp 
  2  add tempfile '/u01/app/oracle/oradata/orcl/temp01a.dbf' size 10m;

Tablespace altered.

YS@orcl 07-SEP-14>select tablespace_name,file_name from dba_temp_files;


TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------------
TEMP            /u01/app/oracle/oradata/orcl/temp01a.dbf
TEMP            /u01/app/oracle/oradata/orcl/temp01.dbf


查看临时文件的大小，自动扩展性：
SYS@orcl 07-SEP-14>select file_name,tablespace_name,bytes/1024/1024||'MB',
  2  autoextensible from dba_temp_files;


FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ---------------
BYTES/1024/1024||'MB'                      AUT
------------------------------------------ ---
/u01/app/oracle/oradata/orcl/temp01a.dbf           TEMP
10MB                                       NO


/u01/app/oracle/oradata/orcl/temp01.dbf            TEMP
25MB                                       YES


oracle数据库不能drop默认的临时表空间
SYS@orcl 07-SEP-14>drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

创建新的临时表空间
SYS@orcl 07-SEP-14>create temporary tablespace temp2 
  2  tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 20m
  3  reuse autoextend on next 1m maxsize unlimited;

Tablespace created.

改变缺省的临时表空间为temp2
SYS@orcl 07-SEP-14>alter database default temporary tablespace temp2;

Database altered.

查看默认的临时表空间：
SYS@orcl 07-SEP-14>select property_name,property_value 
  2  from database_properties
  3  where property_name='DEFAULT_TEMP_TABLESPACE';


PROPERTY_NAME                       PROPERTY_VALUE
----------------------------------- -----------------------------------
DEFAULT_TEMP_TABLESPACE             TEMP2

SYS@orcl 07-SEP-14>drop tablespace temp;

Tablespace dropped.

SYS@orcl 07-SEP-14>select file_name,tablespace_name from dba_temp_files;


FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ---------------
/u01/app/oracle/oradata/orcl/temp02.dbf            TEMP2


在oracle数据库中，我们可以创建多个临时表空间，并把它们组成一个临时表空间组，这样应用数据用于排序时可以使用组里的多个临时表空间，一个临时表空间组至少有一个临时表空间，其最大个数是没有限制的。注意，组的名字不能和其中某个表空间的名字相同。
临时表空间组是在创建临时表空间时通过指定group字句创建的，如果删除组中所有的临时表空间，那么这个组也将会消失。
我们可以把一个表空间从 一个组转移到另一个组，也可以从一个组中删除临时表空间或者添加临时表空间。
使用临时表空间组有如下优点：
1.可以同时指定多个临时表空间，避免由于临时表空间不足造成的磁盘排序问题。
2.当一个用户同时有多个会话时，可以使得它们使用不同的临时表空间。
3.并行操作中，不同的从属进程可以使用不同的临时表空间。

SYS@orcl 07-SEP-14>create temporary tablespace tempts1 tempfile
  2  '/u01/app/oracle/oradata/orcl/tempts01.dbf' size 5m tablespace group group1;

Tablespace created.

SYS@orcl 07-SEP-14>create temporary tablespace tempts2 tempfile
  2  '/u01/app/oracle/oradata/orcl/tempts02.dbf' size 5m tablespace group group2;

Tablespace created.

查看临时表空间组dba_tablespace_groups视图：
SYS@orcl 07-SEP-14>select * from dba_tablespace_groups;


GROUP_NAME      TABLESPACE_NAME
--------------- ---------------
GROUP1          TEMPTS1
GROUP2          TEMPTS2


在临时表空间中进行shrink(oracle11g新功能)

查询当前临时表空间和临时数据文件：
SYS@orcl 08-SEP-14>col tablespace_name for a15
SYS@orcl 08-SEP-14>col file_name for a35
SYS@orcl 08-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
  2  total-used as "free|MB",
  3  total as "total|MB"
  4  from 
  5  (select tablespace_name,sum(bytes_used)/1024/1024 used
  6  from gv_$temp_space_header
  7  group by tablespace_name) temp_used,
  8  (select file_name,tablespace_name,sum(bytes)/1024/1024 total
  9  from dba_temp_files
 10  group by tablespace_name,file_name) temp_total
 11  where temp_used.tablespace_name=temp_total.tablespace_name;


TABLESPACE_NAME FILE_NAME                              free|MB   total|MB
--------------- ----------------------------------- ---------- ----------
TEMPTS2         /u01/app/oracle/oradata/orcl/tempts          4          5
                02.dbf


TEMPTS1         /u01/app/oracle/oradata/orcl/tempts          4          5
                01.dbf


TEMP2           /u01/app/oracle/oradata/orcl/temp02         18         20
                .dbf


将temp2表空间收缩为15m：
SYS@orcl 08-SEP-14>alter tablespace temp2 shrink space keep 15m;

Tablespace altered.
再次查看临时表空间和临时数据文件：
SYS@orcl 08-SEP-14>select temp_used.tablespace_name,temp_total.file_name,
  2  total-used as "free|MB",
  3  total as "total|MB"
  4  from 
  5  (select tablespace_name,sum(bytes_used)/1024/1024 used
  6  from gv_$temp_space_header
  7  group by tablespace_name) temp_used,
  8  (select file_name,tablespace_name,sum(bytes)/1024/1024 total
  9  from dba_temp_files
 10  group by tablespace_name,file_name) temp_total
 11  where temp_used.tablespace_name=temp_total.tablespace_name;


TABLESPACE_NAME FILE_NAME                              free|MB   total|MB
--------------- ----------------------------------- ---------- ----------
TEMPTS2         /u01/app/oracle/oradata/orcl/tempts          4          5
                02.dbf


TEMPTS1         /u01/app/oracle/oradata/orcl/tempts          4          5
                01.dbf


TEMP2           /u01/app/oracle/oradata/orcl/temp02         15         16
                .dbf
temp2的数据文件缩小为15m。




[oracle@localhost orcl]$ ll
total 156872
-rw-r----- 1 oracle oinstall 52429312 Sep  8 00:44 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Sep  8 00:44 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Sep  8 01:02 redo03.log
-rw-r----- 1 oracle oinstall 16785408 Sep  8 00:55 temp02.dbf
-rw-r----- 1 oracle oinstall  5251072 Sep  7 21:27 tempts01.dbf
-rw-r----- 1 oracle oinstall  5251072 Sep  7 21:28 tempts02.dbf


oracle的临时表不占用任何表空间，而且不同的session之间互相看不到对方的数据。
在会话结束后表中的数据会自动清空，如果选了delete rows，则在提交的时候即清空数据，preserve则一直到会话结束。

commit后保留临时数据：
SYS@orcl 08-SEP-14>create global temporary table tablename(
  2  col1 varchar2(10),
  3  col2 number
  4  )on commit preserve rows;

Table created.

commit后不保留临时数据：
SYS@orcl 08-SEP-14>create global temporary table tablename1(
  2  col1 varchar2(10),
  3  col2 number
  4  )on commit delete rows;

Table created.