临时表空间主要用于排序、哈希等操作，另外像创建索引、临时表以及部分聚合函数一般也都会用到临时表空间(若数据规模较小直接在PGA内存中完成，当PGA无法满足要求的情况下才会使用临时表空间)。临时表空间由系统自动管理，一般不需要人为干预，当占用临时表空间的操作完成后，由系统进程SMON完成对其进行清理，释放临时段。由于其系统自动管理特性，人为干预因素较少，因此大部分数据库管理员对临时表空间关注度不够，对其没有真正了解，在生产系统出现问题的时候不能从容应对。以下是日常工作中总结的关于临时表空间的经验和教训，和大家分享一下，如有不对，欢迎指正，谢谢。

临时数据文件空间

当创建临时表空间过程或者向临时表空间增加文件，不知大家发现没有，无论临时文件有多大，执行速度特别快。究其原因临时文件是一种特殊的文件---稀疏文件，英文名sparse files，稀疏文件在创建过程中并不真正分配磁盘块，因此即便文件创建了，其相应的磁盘空间并未被分配，只有在有真正的数据插入进来时，才会被分配磁盘块。具体实验过程如下：

点击(此处)折叠或打开

SQL>set timing on
SQL> CREATE TABLESPACE PERMA01 DATAFILE '/u01/app/oracle/oradata/orcl/perma001.dbf' size 500M;
Tablespace created.
Elapsed: 00:00:06.69
SQL> CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE '/u01/app/oracle/oradata/orcl/temp001.dbf' size 500M;
Tablespace created.
Elapsed: 00:00:00.16

从时间对比，可以发现创建一个相同大小的临时表空间比永久表空间快很多。再看一下两者的大小：

点击(此处)折叠或打开

oracle@linux-qtx3:~> du -sh /u01/app/oracle/oradata/orcl/temp001.dbf
1.1M /u01/app/oracle/oradata/orcl/temp001.dbf
oracle@linux-qtx3:~> du -sh /u01/app/oracle/oradata/orcl/perma001.dbf
501M /u01/app/oracle/oradata/orcl/perma001.dbf

空间上可以看到永久表空间确实像SQL语句一样占用了500M的空间，而临时表空间对应文件只占用了1.1M，此空间为管理内容信息，当数据库需要用临时表空间的时候，临时文件会增加，操作系统会再分配磁盘空间。对于临时文件磁盘空间延后分配问题，存在一定的生产隐患，需要引起足够的重视。具体生产隐患在哪儿？比如为配合业务，在数据库创建了一个1G大小的临时表空间，语句执行完成提示表空间已创建，因为临时数据文件稀疏文件的特性，此时操作系统并未真正分配空间，也许该文件系统只有100M的剩余空间。那么这个时候恰巧一条特别占用临时表空间的语句要执行，当临时文件增长到100M的时候，已经无法再分配空间，导致语句执行失败，出现如下错误：
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01
因此临时数据文件创建后一定要在文件系统预留相关的空间，防止临时数据文件突增无法申请到磁盘空间。

临时表空间使用率

参考永久表空间使用率语句，临时表空间使用率的语句如下（注：以下语句仅适用于11G以上数据库包含11g）：

点击(此处)折叠或打开

select t1.TBS_NAME, round(t1.TOTAL_BYTES/1024/1024,2) || 'M' "TOTAL(M)",round(nvl(t2.FREE_BYTES,0)/1024/1024,2) || 'M' "FREE(M)",
round(100*(t1.TOTAL_BYTES-nvl(t2.FREE_BYTES,0))/t1.TOTAL_BYTES,2) || '%' "USED%"
from
(select t.tablespace_name TBS_NAME,sum(bytes) TOTAL_BYTES from dba_temp_files t group by t.TABLESPACE_NAME) t1,
(select t.tablespace_name TBS_NAME,sum(free_space) FREE_BYTES from dba_temp_free_space t group by t.TABLESPACE_NAME) t2
where t1.tbs_name=t2.tbs_name(+)
order by (t1.TOTAL_BYTES-nvl(t2.FREE_BYTES,0))/t1.TOTAL_BYTES desc;
TBS_NAME TOTAL(M) FREE(M) USED%
------------------------------ ----------------------------------------- ----------------------------------------- ----------------------
TEMP01 500M 499M .2%

对于10g数据库，可以使用如下语句监控，此语句对11g也使用

点击(此处)折叠或打开

SYS>select t.CURRENT_USERS,t.TOTAL_BLOCKS,t.USED_BLOCKS,t.FREE_BLOCKS,round(t.USED_BLOCKS/t.TOTAL_BLOCKS,2)||'%'"USED%" from v$sort_segment t;
CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS USED%
------------- ------------ ----------- ----------- ------------------------------
0 2176 0 2176 0%

下面创建一个数据量较大的表T进行试验，具体过程如下：

点击(此处)折叠或打开

SQL> SELECT count(*) FROM t;
COUNT(*)
----------
8128000

重启打开一个会话，执行SELECT * FROM t order by 1;再其他会话查看表空间的使用率

点击(此处)折叠或打开

SQL> select t1.TBS_NAME, round(t1.TOTAL_BYTES/1024/1024,2) || 'M' "TOTAL(M)",round(nvl(t2.FREE_BYTES,0)/1024/1024,2) || 'M' "FREE(M)",
2 round(100*(t1.TOTAL_BYTES-nvl(t2.FREE_BYTES,0))/t1.TOTAL_BYTES,2) || '%' "USED%" from
3 (select t.tablespace_name TBS_NAME,sum(bytes) TOTAL_BYTES from dba_temp_files t group by t.TABLESPACE_NAME) t1,
4 (select t.tablespace_name TBS_NAME,sum(free_space) FREE_BYTES from dba_temp_free_space t group by t.TABLESPACE_NAME) t2
5 where t1.tbs_name=t2.tbs_name(+)
6 order by (t1.TOTAL_BYTES-nvl(t2.FREE_BYTES,0))/t1.TOTAL_BYTES desc;
......
SQL> /
TBS_NAME TOTAL(M) FREE(M) USED%
------------------------------
TEMP01 500M 106M 78.8%
Elapsed: 00:00:00.00
SQL> /
TBS_NAME TOTAL(M) FREE(M) USED%
------------------------------
TEMP01 500M 76M 84.8%
Elapsed: 00:00:00.01
SQL> /
TBS_NAME TOTAL(M) FREE(M) USED%
------------------------------
TEMP01 500M 45M 91%

排序操作的确一直在使用临时表空间，随着语句的执行，使用率一直在增加。当操作完成后，再次查看临时表空间使用率为0%，说明系统已经自动回收。再翻过来看一下具体磁盘的占用情况：

点击(此处)折叠或打开

oracle@linux-qtx3:~> du -sh /u01/app/oracle/oradata/orcl/temp001.dbf
501M /u01/app/oracle/oradata/orcl/temp001.dbf

临时文件磁盘空间一旦分配后不会被回收。

定位使用临时表空间语句

点击(此处)折叠或打开

select t.USERNAME,t. SESSION_ADDR ,t.SESSION_NUM,t.SQL_ID,t.TABLESPACE,t.CONTENTS,t.SEGTYPE,t.BLOCKS from v$tempseg_usage t
USERNAME SESSION_ADDR SESSION_NUM SQL_ID TABLESPACE CONTENTS SEGTYPE BLOCKS
------------------------------ ---------------- ----------- ------------- ------------------------------- --------- --------- ----------
SCOTT 000000007F7AAD08 35 2zj5pgsgs6yhu TEMP01 TEMPORARY SORT 35200

上述语句可以精确定位到SQL的相关ID以及ADDR，结合v$sql或者v$sqlarea等试图可轻松找到SQL语句的具体内容。

总之：
1.因为稀疏文件的特性，请确保临时表空间对应文件系统有相应的空间预留

2.查询已分配空间的临时表空间使用情况以及具体哪个会话和sql正在使用临时表空间语句如下

select t1.TBS_NAME, round(t1.TOTAL_BYTES/1024/1024,2) || 'M' "TOTAL(M)",round(nvl(t2.FREE_BYTES,0)/1024/1024,2) || 'M' "FREE(M)",

round(100*(t1.TOTAL_BYTES-nvl(t2.FREE_BYTES,0))/t1.TOTAL_BYTES,2) || '%' "USED%"

from

(select t.tablespace_name TBS_NAME,sum(bytes) TOTAL_BYTES from dba_temp_files t group by t.TABLESPACE_NAME) t1,

(select t.tablespace_name TBS_NAME,sum(free_space) FREE_BYTES from dba_temp_free_space t group by t.TABLESPACE_NAME) t2

where t1.tbs_name=t2.tbs_name(+)

order by (t1.TOTAL_BYTES-nvl(t2.FREE_BYTES,0))/t1.TOTAL_BYTES desc;
或者
select t.CURRENT_USERS,t.TOTAL_BLOCKS,t.USED_BLOCKS,t.FREE_BLOCKS from v$sort_segment t;

select t.USERNAME,t. SESSION_ADDR,t.SESSION_NUM,t.SQL_ID,t.TABLESPACE,t.CONTENTS,t.SEGTYPE,t.BLOCKS from v$tempseg_usage t
或者
select t.USERNAME,t. SESSION_ADDR,t.SESSION_NUM,t.SQL_ID,t.TABLESPACE,t.CONTENTS,t.SEGTYPE,t.BLOCKS from v$sort_usage t
（V$TEMPSEG_USAGE是v$sort_usage的同义词）
3.当监控到临时表空间使用率100%，但相关文件系统未满的时候，请检查临时数据文件是否设置过小且AUTOEXTENSIBLE设置为AUTOEXTEND OFF。针对此情况可适当增加临时文件大小或者添加临时数据文件。
另外,由于临时表空间并不真正存储永久性对象，在RMAN备份中并不备份临时表空间，其实即便备份也是在浪费时间，因为Oracle根本不会去恢复临时表空间。

ITPub博客

表空间序列之临时表空间

临时数据文件空间

临时表空间使用率

定位使用临时表空间语句

最新文章