ORACLE查询临时表空间使用率一直是100%的一种原因

文章中关键技术解释取自潇湘隐者大神的博客园

地址:http://www.cnblogs.com/kerrycode/p/5797233.html

近期公司一个项目的oracle数据库需要优化，在优化过程中同事发现了一个问题：
用同事给的sql进行查询SYSAUX,&＃8217;SYSTEM,UNDOTBS1,TEMP表空间时，发现TEMP表空间使用率为100%

SELECT * FROM (
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1)
WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP');


如图所示：

TEMP为100%

用另外一个SQL查询TEMP表空间的实际使用情况，发现实际上TEMP已经被oracle回收，实际利用率为0%

SELECT D.tablespace_name,
SPACE "SUM_SPACE(M)",
blocks "SUM_BLOCKS",
used_space "USED_SPACE(M)",
Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - used_space "FREE_SPACE(M)"
FROM (SELECT tablespace_name,
Round(SUM(bytes) / (1024 * 1024), 2) SPACE,
SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace,
Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+)
AND D.tablespace_name in ('TEMP', 'TEMP1')


实际为0%

当然在分析这个问题的时候发现自己当时建立表空间并且指定默认表空间的时候，错误的将默认表空间指给了oracle建库的时候的临时表空间，而自己特意划出来的TEMP01表空间给的10G表空间一点都没用上。。。。。于是赶紧先把临时表空间切到TEMP01上。

两种查询结果不一致，让我感觉很好奇，于是在网上找一些资料，最后翻到潇湘大神的博客，给出的解释为：

视图v$temp_space_header显示的是每一个temp文件在某一个时刻使用过的最大大小，从本质上说，它显示的是每一个tempfile的初始化大小，而不是实际分配的块大小。
所以说从视图v$temp_space_header获取的数据其实并不是实际使用的大小，它是不准确的。那么肯定有人会问，脚本里面不是访问的GV_$TEMP_SPACE_HEADER视图吗? 跟这个视图v$temp_space_header有关系吗？ 答案是有关系，他们的数据来源是一致的，也就是说来自相同的内部表。

呵呵，看到这里就应该能明白了，原来第一个语句中查询的数据库视图的信息是记录了temp文件在某一时刻使用过的最大大小，这个数据库刚建立的时候进行过impdp操作，所以肯定涉及大量的数据读写，当然就会将oracle自带的临时表空间占满，并且默认的临时表空间是可自动扩展的，这样肯定有一个时刻占用率为100%，后续即使oracle释放了表空间，那么按照MOS解释，v$temp_space_header视图肯定记录了达到100%时候的情况，这样用第一个语句无论怎么查询，TEMP表空间都会是100%。

根据这个现象，想到公司很多同事都遇到过临时表空间一到100%，就疯狂的往上扩数据文件，但是临时表空间真的满了吗？通过这个例子来看，未必。也许是一直以来查询临时表空间的方式就有问题呢？

分享这个SQL，让之前没深入了解过的人参考。