查看Oracle回滚段的周详情况，连同对性能的影响:
select a.name, b.extents, b.rssize,b.xacts,b.waits,b.gets,b.optsize,b.status
from v$rollname a,v$rollstat b
where a.usn = b.usn
[转载]临时表空间问题
[整理]关于temporary tablespace，翻译一篇
入门的
文档。
经常看到有人说看到temporary tablespace空间不释放等等的问题，整理一篇metalink上的相关文章，入门级的，还算浅显吧。基本上就是翻译了。
有疑问的欢迎讨论。
参考metalink：Temporary Segments Are Not Being De-Allocated After a Sort（Note:1039341.6）
sort之后临时段不回收
描述
======
当你监控temporary tablespace 的空闲空间，发现空闲空间并没有随着sort的结束而增加，甚至当前
数据库没有任何客户端连接也是如此：仍然有大量的temporary类型的extent存在于temporary tablespace，有时候用户会在运行事务的时候遇到ora-1652（在temp表空间上），这个错误表明没有足够的空间，但是当用户的查询失败后，smon并没有去清理临时段。
Scope & Application
===================
如果TEMPORARY TABLESPACE的类型是TEMPORARY，TEMPORARY TABLESPACE里的使用过的空间是不会被释放的，除非shutdown。
如果是PERMANENT，SMON会在process不再使用临时段之后去做清理。
如果使用TEMPORARY类型的临时表空间，数据库刚刚startup后，第一个使用TEMPORARY tablespace进行排序的statement会创一个建sort segment，这个segment不会被释放，除非数据库restart，可以用V$SORT_SEGMENT察看当前的已分配了的sort segments地使用情况。
如果是用PERMANENT tablespace作排序，由smon负责在statement结束之后删除被创建的temporary segments，这样空间可以被其他对象使用。
使用如下查询：
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE ,
TABLESPACE_NAME
from DBA_SEGMENTS
where SEGMENT_TYPE = 'TEMPORARY';
to give results similar to:
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
----- ------------ ------------ ---------------
SYS 4.2 TEMPORARY TEMP
Note: segment_name and tablepace_name are likely to be different.
tablespace的类型可以用如下查询：
select TABLESPACE_NAME,
CONTENTS
from DBA_TABLESPACES
where TABLESPACE_NAME in (<list of tablespaces from above> );
to give results similar to:
TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP TEMPORARY
TEMP1 PERMANENT
解释
=====
1)
对于使用了TEMPORARY类型的TEMPORARY TABLESPACE，临时段被使用后不被回收是正常的行为（7.3以后），这时如果有ora-1652发生，说明临时表空间是真得不够:-)
系统中的临时段在oracle startup之后被创建，并只有在oracle shutdown的时候被释放，如果存在大量的extent，一个可能的原因是你的storage子句的设置有问题。
考虑到性能原因，当一个temporary extent被分配的时候，tablespace会做一个标记，操作结束之后这个extent不会被释放或回收，相应的，这个extent被简单的标志为free，对于后面的sort操作是available的，这样就省去了系统分配和回收temporary extent的负载。
2）
对于在PERMANENT的tablespace里面使用temporary segment（别让概念搞昏了，呵呵），记得检查parameter file里面没有设置如下参数：
event="10061 trace name context forever, level 10"
event="10269 trace name context forever, level 10"
这两个参数禁止smon去做temporary segment的clean up和coalescing，在oraus.msg里面可以看到具体的
定义：
10061, 00000, "disable SMON from cleaning temp segments
10269, 00000, "Don't do coalesces of free space in SMON"
// *Cause: setting this event prevents SMON from doing free space coalesces
（正常情况下，smon会负责定期做temporary segment的clean up和coalescing。具体作的方式metalink可以找到）
通过查询V$SORT_SEGMENT来看temp segment是free还是being used
For example:
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
>>> DURING
the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
---------------- ------------ ----------- -----------
TEMP 590 590 0
>>> AFTER the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
--------------- ------------ ------------ -----------
TEMP 590 0 590
通过这个查询可以看到在做sort的时候，segment标识为used，sort结束后，标志为free。实际上这时候相应的extents都被释放回SEP（sort extent pool）里面。（SEP是SGA里面存放temp extent的部分，具体描述在metalink也可以找到）
最后，把tablespace的类型改为temporary可以这样：
ALTER TABLESPACE <TABLESPACE_NAME> TEMPORARY;
如果temp被扩大 关于回收temp空间大小 9i就是shutdown在startup也不能 回收空间
只能重建才能回收空间
temporary 类型的temporary tablespace，是没有回收这个概念，用完了的temp extent，只是标志为free，不回收。temp表空间为autoextend,所以一直扩张.
看我翻译的文章，当你使用的temporary tablespace 的类型是PERMANENT的时候，smon才会负责回收空间。如果你的类型时TEMPORARY，smon不会起作用，这跟表空间是否 locally managed (还是字典管理表空间)是不同概念吧。
可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1，如：
alter tablespace temp
default storage(pctincrease 1);
这样smon便会将自由范围自动合并。也可以手工合并自由范围：
alter tablespace temp coalesce; (注:这个仅仅对使用字典管理的temp空间有用。)
Oracle临时表空间为何暴涨？
昨天在做测试的时候发现一个非常奇怪的问题：在程序的查询模块中做查询的时候，开始速度很快，但是过了一段时间以后速度就变慢，最后干脆就报错，不工作了。在排错的过程中，发现Oracle临时表空间暴涨，达到了几十个GB，在Oracle中对Session进行跟踪，发现磁盘空间还在不停的消耗，几乎是每隔5s，临时表空间就会增长500MB左右，最后报错的原因应该是因为没有磁盘空间可以分配造成的。这是一件十分恐怖的事情。
我们知道Oracle临时表空间主要是用来做查询和存放一些缓存的数据的，磁盘消耗的一个主要原因是需要对查询的结果进行排序，如果没有猜错的话，在磁盘空间的（内存）的分配上，Oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB，如果还有增长，那么依此类推，临时表空间始终保持在一个最大的上限。像上文提到的恐怖现象经过分析可能是以下几个方面的原因造成的。
1. 没有为临时表空间设置上限，而是允许无限增长。但是如果设置了一个上限，最后可能还是会面临因为空间不够而出错的问题，临时表空间设置太小会影响性能，临时表空间过大同样会影响性能，至于需要设置为多大需要仔细的测试。
2.查询的时候连表查询中使用的表过多造成的。我们知道在连表查询的时候，根据查询的
字段和表的个数会生成一个迪斯卡尔积，这个迪斯卡尔积的大小就是一次查询需要的临时空间的大小，如果查询的字段过多和数据过大，那么就会消耗非常大的临时表空间。
3.对查询的某些字段没有建立索引。Oracle中，如果表没有索引，那么会将所有的数据都复制到临时表空间，而如果有索引的话，一般只是将索引的数据复制到临时表空间中。
针对以上的分析，对查询的语句和索引进行了优化，情况得到缓解，但是需要进一步测试。
www.shuangxian.com 
