ORACLE 临时表空间使用率过高的原因及临时解决方案

数据库temp临时表空间增大，一般在数据安装时设置临时表空间大小，默认的情况下都是设置为自动增长。这样会引起一个问题：在数据库使用中temp表空间会自动扩展的越来越大，造成磁盘空间使用不足。

引起临时表空间增大主要使用在以下几种情况：
1、order by or group by (disc sort占主要部分)；
2、索引的创建和重创建；
3、distinct操作；
4、union & intersect & minus <wbr>sort-merge joins；</wbr>

5、Analyze 操作；
6、有些异常也会引起TEMP的暴涨。

<wbr></wbr>

解决temp临时表空间大小问题：

一.直接缩小temp表空间大小

alter database tempfile 'd:powerdmsdb\dmsdb\temp01.dbf' resize 1024M;

此语句会直接修改temp表空间的大小，但可能会执行不成功，因为当temp使用率为100%或当前有会话占用时，temp表空间是无法缩小的。

查询当前会话：

SELECT<wbr>se.username,<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>se.sid,<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>se.serial#,<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>se.sql_address,<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>se.machine,<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>se.program,<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>su.tablespace,<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>su.SEGTYPE,<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>su.CONTENTS<br><wbr><wbr>FROM<wbr>v$session<wbr>se,<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>v$sort_usage<wbr>su<wbr><br><wbr>WHERE<wbr>se.saddr=su.session_addr<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

利用此sql语句查询当前会话，然后kill当前会话：

Alter<wbr>system<wbr>kill<wbr>session<wbr>'sid,serial#'</wbr></wbr></wbr></wbr>

执行此语句后再对temp表空间resize空间大小就可以了。

注：此方法只是对temp表空间做临时性的缩小，以后还会继续增大。<wbr></wbr>

方法二：

对temp临时表空间重建并设置增长上限值，设置这个值时要预先估算设置合理，不然当增大到此值时会出错，在合理的范围内增加到上限值时数据库会自动释放temp临时表空间。

重建temp临时表空间：

1.--启动数据库

startup<wbr></wbr>

2.--创建中转临时表空间
create TEMPORARY TABLESPACE TEMP2 TEMPFILE 'D:\PowerDmsDB\DMSDB\TEMP02.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M;<wbr><br> 3.--改变缺省临时表空间 为刚刚创建的新临时表空间temp2<br> alter database default temporary tablespace temp2;<wbr><wbr><wbr><br> 4.--删除原来临时表空间<br> drop tablespace temp including contents and datafiles;</wbr></wbr></wbr></wbr>

5.--重新创建临时表空间
create TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\PowerDmsDB\DMSDB\TEMP01.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M;
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br> 6.--重置缺省临时表空间为新建的temp表空间<br> alter database default temporary tablespace temp;<wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

7.--删除中转用临时表空间
drop tablespace temp2 including contents and datafiles;

8.--重新指定用户表空间为重建的临时表空间
alter user zxd temporary tablespace temp;<wbr></wbr>

至此临时表空间增长过大可以更改完成。<wbr></wbr>

下面是查询在sort排序区使用的执行耗时的SQL:
Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as<wbr><wbr><wbr> Space,tablespace,segtype,sql_text<br> from v$sort_usage su,v$parameter p,v$session se,v$sql s<br> where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr<br> order by se.username,se.sid</wbr></wbr></wbr>

此语句可以做跟踪查看分析时用。