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

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

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

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

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

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

　　alter database tempfile 'd：powerdmsdb\dmsdb\temp01.dbf' resize 1024M；

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

　　查询当前会话：

　　SELECT se.username，se.sid，se.serial#，se.sql_address，se.machine，se.program，su.tablespace，su.SEGTYPE，su.CONTENTS FROM v$session se，v$sort_usage su WHERE se.saddr=su.session_addr

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

　　Alter system kill session 'sid，serial#'

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

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

　　方法二：

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

　　重建temp临时表空间：

　　1.——启动数据库

　　startup

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

　　5.——重新创建临时表空间create TEMPORARY TABLESPACE TEMP TEMPFILE 'D：\PowerDmsDB\DMSDB\TEMP01.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M；

　　6.——重置缺省临时表空间为新建的temp表空间alter database default temporary tablespace temp；

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

　　8.——重新指定用户表空间为重建的临时表空间alter user zxd temporary tablespace temp；

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

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

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