之前一直以为临时表空间是排序引起的 今天发现 如果是查询数据量大 不排序也会有这个问题的 百度有片文章讲的不错

在数据库的学习中 发现公司生产数据库的默认表空间temp使用到了30g 使用率到了100% 待调整为32g后 使用率还是

100% 导致磁盘空间使用紧张 根据临时哦空间的主要是对临时表数据进行排序和缓存数据等特性 待数据库重启后 temp会

自动释放 于是想通过重启数据库的方式来缓解这种情况 但是重启数据库之后 发现临时表空间temp的使用率还是100% 一点

没变 虽然运行中应用暂时没有报什么错误 但是这在一定程度上存在一定的隐患 有待解决该问题 由于临时表空间主要使用在

以下几种情况

1 order by or group by (disk 占主要部分)

2 索引的创建和重创建

3 distinct操作

4 union&insersect&minus sort-merge joins

5 Anaylze 操作

6 有些异常也会引起temp的暴涨。

Oracle临时表空间暴涨的现象经过分析可能是以下几个方面的原因造成的

1 没有临时表空间设置上限 而是允许无限增长 但是如果设置了一个上限

最后还是会面临因为空间不够而出错的问题 临时表空间设置太小会影响

性能 太大同样会影响性能 至于需要设置为多少需要仔细的测试

2 查询的时候表过多造成的 我们知道 在连表查询的时候 根据查询的

字段和表的个数会生成一个笛卡尔积 这个笛卡尔积的大小就是一次查询需要

的临时表空间的大小 如果查询的字段过多和数据过大 那么就会消耗非常大的

临时表空间

3．对查询的某些字段没有建立索引 oracle中 如果表没有索引 那么会将所有

的数据都复制到临时表空间 而如果有索引的话 一般只是将索引的数据复制到临时表

空间中

针对以上的分析 对查询的语句和索引进行了优话 情况得到缓解 但是需要进

一步测试

总结：

1 sql语句是会影响到磁盘的消耗的 不当的语句会造成磁盘爆满

2 对查询语句需要仔细的规划 不要想当然的定义一个查询语句 特别是在用户自己定义

查询语句 特别是在可以提供用户自定义查询的软件中

3 仔细规划表索引 如果临时表空间是temporary的空间不会释放 只是在sort结束后被

标记为free的 如果是permance负责在sort结束后释放 都不用去手工释放的

v$sort_usage和v$sort_segment

通过查询相关资料 发现解决方案有如下几种

一 重建临时表空间 temp

Temporary tablespace是不能直接drop默认的临时表空间的 不过我们可以

通过以下方法达到。查看目前的temporary tablespace

Select name from v$tempfile;

Select username,temporary_tablespace from dba_users

1 创建中转临时表空间

Create temporary tablespace temp1 tempfile

2 改变缺省临时表空间 为刚刚创建的新临时表空间temp1

Alter database default temporary tablespace temp1

3 删除原来临时表空间

Drop tablespace temp including contents and datafiles;

4 重新创建临时表空间

Create temporary tab lespace temp TEMPFILE ‘D:ORACLE

5 重置缺省表空间为新建的temp表空间

Alter database default temporary tablespace temp;

6 删除中转用临时表空间

Drop tablespace temp1 including contents and datafiles

以上的方法只是暂时释放了临时表空间的磁盘占用空间 是治标不是治本的方法

真正治本的方式是找出数据 大的sql语句 然后对其进行优化处理 下面是查询在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 and

s.hash_value=su.sqlhash and s.address=su.sqladdr

order by se.username,se.sid

或是

Select su.username,su.Extents,tablespace.segtype.sql_text

From v$sort_usage su,v$sql s

Where su.sql_id=s.sql_id

注： 如果原临时表空间无用户使用

（select tablespace_name,current_users,total_block,used_blocks,free_blocks/total_blocks from

v$sort_segment) 如果是文件系统可以看看文件的时间戳

我们可以删除该表空间 如果原临时表空间还有用户在使用 你是删除不了这个表空间的 在一次

生产环境的临时表空间切换中 原来临时表空间始终有用户在上面 即使我关闭了前台程序 也还是有

用户 新的临时表空间已经没有用户在使用了 我估计用户进程已经死在原临时表空间了 后来只有重新

启动数据库才能把原来旧的临时表空间给剔除

二 修改参数（这个方案适用于8i及8i以下的版本）

修改一下temp表空间的storage参数 让smon进程关注一下临时段 从而达到清理和temp

表空间的目的

Alter tablespace temp increase 1

Alter tablespace temp increase 0

三 kill session

1 使用如下语句查看一下谁在用临时段

Select se.username ，se.sid ，se.serial# ，se.sqll_ddress ,se.machine ,se.program,

Su.TABLESPACE, su.settype,su.contents from

v$session se,v$sort_usage su where se.addr=su.session_addr

2 kill正在使用段的进程

Alter system kill session ‘sid,serial3’;

3 把temp表回滚一下

Alter tablespace temp coalesce

注： 这处方法 只能针对字典管理表空间（dictonary managed tablespace）

于本地管理表空间（lmt;local managed tablespace）不需要整理的 9i以后

只能创建本地管理的表空间。

Create tablespace test datafile ‘D:

四 使用诊断事件 也是相对有效的一种方法

1 查询诊断事件代码

Sql> select ts# , name from sys.ts#;

TS# NAME

-----------------------------------------

0 SYSTEM

1 UNDOTBS1

2 SYSAUX

3 TEMP

4 USERS

5 UNDOTBS

2 执行清理操作

Sql>alter session set events ‘immediate trace name DROP_SEGMENTS level 4’;

说明 ：temp表空间的ts# + 1=4