ORA-临时表空间不足

ORA-01652临时表空间不足

最近使用ETL执行任务发现经常报一个“临时表空间的问题”，根据公司业务摸索出了一些修改方案。

首先在网上搜索问题解决方案，看到绝大多数都是从重新分配临时表空间或增加或清除。但由于环境原因导致不能轻易去更改分配的相关表空间大小。

另外，我们分配的临时表空间为120G，结合平时基础数据量，基本可以排除继续增加临时表空间这种治标不治本的方法了。

下面我们从业务及SQL语句的方面去发现并解决问题。至于导致临时表空间不足的原因，看了网上的说法应该比我解释的要清楚得多，其实说白了就是在执行更新，插入等操作时我们先通过SELECT搜索出结果才会执行更新等操作，而在执行更新完成之前SELECT出的结果会先存入临时表空间。所以，若分配的临时表空间足够大足够合理，仍出现临时表空间不足的问题，很大原因可能是SQL语句中SELECT查询出来的数据量过大等原因造成。

既然知道了产生原因，那解决方式就要简单得多了。

首先，我们查找自己数据库总的临时表空间大小

--临时表空间使用情况
select total_blocks*(1/128) 总数,free_blocks*(1/128) 可用数
,(total_blocks-free_blocks)*(1/128) 已用数  from v$sort_segment;

然后，在SQL语句执行时，监控每一个SQL语句占用的临时表空间大小

SELECT pr.SQL_TEXT,su.BLOCKS*(1/128) FROM v$sort_usage su,v$session se,v$sqlarea pr 
where su.session_addr=se.saddr
and se.SQL_HASH_VALUE = pr.HASH_VALUE;

通过上面语句可以看到每条占用临时表空间的SQL及其消耗的临时表空间大小，只需要这两步就能精准定位需要改善的SQL。

而修改的方法其实也十分简单。就是将曾经的SQL语句中，数据量大的表拆分出去，可以采用建立临时表分段更新分段插入的方式。

另外，若发现即使SQL中各表的数据量并不大，但仍然发现占用临时表空间很大的现象，可以考虑一下重新进行表分析。

首先，通过all_tables查找相关语句中表的信息，其中有一个名为“NUM_ROWS”的字段，这个字段记录的是你表分析后的数据量条数。若发现这个字段为0，需要验证相关表是否存在数据。等确定相关表确实有数据但相关NUM_ROWS字段为0的情况，需要重新对相关表进行一次表分析。

EXEC DBMS_STATS.GATHER_TABLE_STATS('用户','表名',ESTIMATE_PERCENT=>80,CASCADE=>TRUE,NO_INVALIDATE=>FALSE,DEGREE=>8)

至于表分析视图中具体的参数涵义可以去网上搜索