今天在网上乱逛，突然看到一位牛人写的一个挺好的总结帖，复制些内容过来学习学习！
1、临时表空间的用途
1）disk sort
在了解disk sort排序之前，先来看一段doc（When the WORKAREA_SIZE_POLICY parameter is set to MANUAL, the maximum amount of memory allocated for a sort is defined by the parameter SORT_AREA_SIZE. If the sort operation is not able to completely fit into SORT_AREA_SIZE memory, then the sort is separated into phases.The temporary output of each phase is stored in temporary segments on disk. The tablespace in which these sort segments are created is the users temporary tablespace.When Oracle writes sort operations to disk, it writes out partially sorted
data in sorted runs. After all the data has been received by the sort, Oracle merges the runs to produce the final sorted output. If the sort area is not large enough to merge all the runs at once, then subsets of the runs are merged in several merge passes. If the sort area is larger, then there are fewer, longer runs produced. A larger sort area also means that the
sort can merge more runs in one merge pass.）
从上面doc看来，我自己的理解是排序始终是在内存里完成的，如果要排序的数据量很大，在内存里不能完成，oracle会分阶段来排序，每次先排一部分，并且把排好序的数据临时存放在用户default temporary tablespace中的temp segment上，而临时表空间对应的tempfile属于disk文件，这就是disk sort的由来。具体oracle是如何分阶段来排序的，doc说的还算清楚，只是可能不太好理解。其实上面doc说的不正是排序时常提到的3种情况：optimail，one pass sort，mutli-pass sort
a）optimal
我们知道排序最理想的方式是optimal，也就是需要排序的数据都在内存里放的下，而且内存有足够空间做排序。排序本身的原理可能是相当复杂的，但是大致的说法应该是排序时在内存需要维护一个树状的结构来完成排序，所以假如你有5M的数据需要排序，这时候你需要的内存会远大于5M。
b）one pass sort
假如需要排序的是1到20，但内存一次只能排序5个数据，这时候不得不5个数据做一个排序，每排好一组就放在tempfile上，最后在磁盘上就存在4组数据，这时候If the sort area
is large enough to merge all the runs at once，那么所做的sort就是one pass的。在这个特定的例子里，large enough应该是指有能力一次在内存里做4个数据的排序，及首先在1,2,12,11中能够选出最小的，就是1，然后在接下来的6,2,12,11里选出2, 然后在6,3,12,11中选出3，以此类推
c）mutli-pass sort
在这个例子里，如果内存小到一次只能排序3个数据，那onepass 就做不到了，按照和onepass类似的方法，先在tempfile得到7组数据，然后这时候因为the sort area
is not large enough to merge all the runs at once, then subsets of the runs
are merged in several merge passes.
因此需要把7组数据变成第二阶段的3组，然后在把这3组数据排序。因为在磁盘上存了2次数据，所以叫multi-pass，内存越小，pass的次数越多，排序需要的时间也就越长。
2）global temporary table
oracle支持两种类型的临时表，之所以称为global，是因为不论是哪个session创建的临时表，该表对所有的session都是可见的，但是数据仅仅对创建临时表的session可见。
oracle支持两种类型的临时表：
a）transaction-specific (the default)
doc中提到的transaction-specific其实就是指在创建临时表时在语法中指定的on commit delete rows，这种类型的临时表在commit之后也就是事务结束以后数据被删除了：
SQL> create global temporary table gt1 as select * from dba_tables;
表已创建。
SQL> select count(*) from gt1;
COUNT(*)
----------
0
b）session-specific
这种临时表指的就是在创建时使用on commit preseve rows子句创建的临时表，它的特点是在事务结束以后数据并没有立即被删除，而是在session结束之后数据被删除的：
SQL> create global temporary table gt3 on commit preserve rows as select * from
dba_tables;
表已创建。
SQL> select count(*) from gt3;
COUNT(*)
----------
1212
在另一个sqlplus窗口看看数据发现不可见：
SQL> select count(*) from gt3;
COUNT(*)
----------
0
SQL>
再回到创建gt3时的窗口：
SQL> insert into gt3 select *from dba_tables;
已创建1213行。
SQL> select count(*) from gt3;
COUNT(*)
----------
2425
SQL> rollback;
回退已完成。
SQL> select count(*) from gt3;
COUNT(*)
----------
1212
SQL> truncate table gt3;
表被截断。
SQL> select count(*) from gt3;
COUNT(*)
----------
0
SQL>
试验发现global temporary table支持rollback，意味着对temporary table的操作是需要占用undo的。而undo是需要保护的，因此使用临时表也会产生undo生成的少量redo。临时表也支持truncate，而doc上说的（DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table）有些莫名其妙。
我们在判断临时表到底是属于那种类型时除了通过数据验证之外，也可以查询数据字典获得：
SQL> select TABLE_NAME ,TEMPORARY , DURATION,tablespace_name,logging from dba_t
ables where table_name in ('GT1','GT2','GT3');
TABLE_NAME T DURATION TABLESPACE LOG
------------------------------ - --------------- ---------- ---
GT3 Y SYS$SESSION NO
GT2 Y SYS$TRANSACTION NO
GT1 Y SYS$TRANSACTION NO
DURATION字段说明了临时表的类型。
退出创建gt3时的session发现数据被清除了：
SQL> insert into gt3 select *from dba_tables;
已创建1213行。
SQL> commit;
提交完成。
SQL> select count(*) from gt3;
COUNT(*)
----------
1213
SQL> disconnect
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
SQL> connect sys/system@dmt as sysdba
已连接。
SQL> select count(*) from gt3;
COUNT(*)
----------
0
临时表和普通的heap表产生的日志对比：
SQL> create table t1 tablespace users as select * from dba_objects where 1=2;
表已创建。
SQL> SELECT b.name , a.value FROM v$sysstat a ,
2 v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like 'redo size%';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 3248648
SQL> insert into t1 select * from dba_objects;
已创建11260行。
SQL> commit;
提交完成。
SQL> SELECT b.name , a.value FROM v$sysstat a ,
2 v$statname b
3 where a.statistic#=b.statistic#
4 and b.name like 'redo size%';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 4780384
SQL> select 4780384 - 3248648 from dual;
4780384-3248648
---------------
1531736
再来看看同样的操作临时表产生的reodo：
SQL> create global temporary table gt1 on commit preserve rows as select * from
dba_objects where 1=2;
表已创建。
SQL> select b.name , a.value from v$sysstat a , v$statname b where a.statistic#=
b.statistic# and b.name like '%redo size%';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1584124
SQL> insert into gt1 select * from dba_objects;
已创建11456行。
SQL> commit;
提交完成。
SQL> select b.name , a.value from v$sysstat a , v$statname b where a.statistic#=
b.statistic# and b.name like '%redo size%';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 1773124
SQL> select 1773124 - 1584124 from dual;
1773124-1584124
---------------
189000
相差的redo：
SQL> select 1531736 - 189000 from dual;
1531736-189000
--------------
1342736
SQL>
通过上面对比我们发现同样的操作使用临时表比普通的表要少产生很多日志；而尽管临时表显示的是nologging，但是还是产生了一些日志，这些日志其实是由undo 产生的，因为临时表支持rollback，意味着对临时表执行dml操作是需要占用undo的，而undo本身也是需要保护的，因此对undo的使用产生了一部分redo。
另外在创建临时表时不能指定tablespace tablespace_name子句，临时表只能使用用户的default temporary tablespace：
SQL> create global temporary table gt2(id int) on commit preserve rows tablespac
e tmp;
create global temporary table gt2(id int) on commit preserve rows tablespace tmp
*
第 1 行出现错误:
ORA-14451: 不受支持的临时表功能
===========================================================================================
临时表空间组
临时表空间组是10g引入的概念，目的是提高同一用户并发session对临时段的请求。我们知道一个临时表空间中只存在一个临时段，当一个session在使用临时段时，其他session再请求临时段时需要等到拥有该临时段的session使用完毕之后才能使用，造成这一问题的根源在于一个用户只能使用一个临时表空间。而临时表空间组的出现大大改善了同一用户并发session对临时段的争夺，因为一个临时表空间组可以包括多了临时表空间，而用户的default temporary tablespace又可以是临时表空间组。其实更直接的说就是临时表空间组的出现使用户能够使用多个临时表空间了。下面做个简单的测试：
SQL> create temporary tablespace temp tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtemp01.dbf' size 10m autoextend on;
表空间已创建。
SQL> create temporary tablespace tmp tempfile 'E:ORACLEPRODUCT10.2.0ORADATA
DMTtmp01.dbf' size 10m reuse autoextend on;
表空间已创建。
SQL> select * from dba_tablespace_groups;
未选定行
SQL> alter tablespace temp tablespace group gp1;
表空间已更改。
SQL> alter tablespace tmp tablespace group gp1;
表空间已更改。
SQL> alter database default temporary tablespace gp1;
数据库已更改。
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GP1 TEMP
GP1 TMP
SQL> alter user xys temporary tablespace gp1;
用户已更改。
SQL> connect xys/manager@dmt
已连接。
SQL> create table t as select * from dba_objects;
表已创建。
SQL> insert into t select * from t;
已创建11260行。
SQL> insert into t select * from t;
已创建22520行。
SQL> insert into t select * from t;
已创建45040行。
SQL> commit;
提交完成。
SQL> create table tt as select * from t;
表已创建。
分别打开两个plsql developer以用户xys登录对表t和tt同时进行排序，之后通过如下查询监视对临时表空间的使用情况，发现来自同一用户xys的不同session同时排序时使用了同一临时表空间组内的不同临时表空间，这样大大减少了之前同一用户只能使用一个临时表空间而产生的请求临时段的等待时间：
SQL> select operation_type ,sql_id , tablespace,tempseg_size,number_passes from
v$sql_workarea_active;
OPERATION_ SQL_ID TABLESPACE TEMPSEG_SIZE NUMBER_PASSES
---------- ------------- ---------- ------------ -------------
SORT (v2) 3n26jhzw1fvr1 TEMP 10485760 1
SORT (v2) 7z5ttxyv6c604 TMP 10485760 1
SQL> select sql_text from v$sql where sql_id in (select sql_id from v$sql_workar
ea_active);
SQL_TEXT
--------------------------------------------------------------------------------
select * from t order by object_name desc
select * from t order by object_id desc
另外需要说明的是临时表空间组本身不能创建，只要把存在的临时表空间加入按照命名规则命名的临时表空间组中就可以了，临时表空间组随之被创建，就象上面操作的那样：
SQL> alter tablespace temp tablespace group gp1;
表空间已更改。
SQL> alter tablespace tmp tablespace group gp1;
表空间已更改。
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GP1 TEMP
GP1 TMP
gp1随着temp和tmp的加入被创建随着temp和tmp的脱离而被删除：
SQL> alter tablespace temp tablespace group '';
表空间已更改。
SQL> alter tablespace tmp tablespace group '';
表空间已更改。
SQL> select * from dba_tablespace_groups;
未选定行
SQL>