oracle的临时表空间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中，如果表没有索引，那么会将所有的数据都复制到临时表空间，而如果有索引的话，一般只是将索引的数据复制到临时表空间中。
       针对以上的分析，对查询的语句和索引进行了优化，情况得到缓解，但是需要进一步测试。

       总结：
       1.SQL语句是会影响到磁盘的消耗的，不当的语句会造成磁盘暴涨。
       2.对查询语句需要仔细的规划，不要想当然的去定义一个查询语句，特别是在可以提供用户自定义查询的软件中。
       3.仔细规划表索引。
       如果有那位高人遇到过类似的问题，请给予您的建议。

 

 Oracle:由临时表空间迅速增长所想到的... 
1、临时表空间的作用：

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。

当临时表空间不足时，表现为运算速度异常的慢，并且临时表空间迅速增长到最大空间（扩展的极限），并且一般不会自动清理了。

2、迅速增长的原因：

一般原因是sql算法不正确，可能导致出现了迪卡尔积。

另外一个原因是视图的创建问题（本人遇到的正是该问题！）。Oracle数据库中多张表联合查询时，极限的表单数量为4张，一般将以为3张！有时需要大于4张表联合查询时，怎么办呢？可以建立子视图：

如view0(联合table0,table1,table2),view1(联合table0,table3,table4),view2(联合table0,table5,table6)

然后将view0、view1、view2联合成view。

这样可以解决临时表空间迅速增长的问题！

以上所述，如有不正确的地方，欢迎大家指正！

 

临时表空间使用注意:
1.临时表空间 是用于在进行排序操作(如大型查询,创建索引和联合查询期间存储临时数据)
每个用户都有一个临时表空间
2.对于大型操作频繁,(大型查询,大型分类查询,大型统计分析等),应指定单独的临时表空间,以方便管理

3.分配用户单独临时表空间,一般是针对 大型产品数据库,OLTP数据库,数据库仓库
对于小型产品不需要单独制定临时表空间,使用默认临时表空间

注:如果临时表空间异常增涨很快,则要考虑是否应用的sql语句产生了笛卡儿积,可以通过stactpack 进行分析查出问题所在,从而保证数据库的正常运行