临时表空间主要用于排序、哈希等操作,另外像创建索引、临时表以及部分聚合函数一般也都会用到临时表空间(若数据规模较小直接在PGA内存中完成,当PGA无法满足要求的情况下才会使用临时表空间)。临时表空间由系统自动管理,一般不需要人为干预,当占用临时表空间的操作完成后,由系统进程SMON完成对其进行清理,释放临时段。由于其系统自动管理特性,人为干预因素较少,因此大部分数据库管理员对临时表空间关注度不够,对其没有真正了解,在生产系统出现问题的时候不能从容应对。以下是日常工作中总结的关于临时表空间的经验和教训,和大家分享一下,如有不对,欢迎指正,谢谢。
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01
因此临时数据文件创建后一定要在文件系统预留相关的空间,防止临时数据文件突增无法申请到磁盘空间。
临时数据文件空间
当创建临时表空间过程或者向临时表空间增加文件,不知大家发现没有,无论临时文件有多大,执行速度特别快。究其原因临时文件是一种特殊的文件---稀疏文件,英文名
sparse files,稀疏文件在创建过程中并不真正分配磁盘块,因此即便文件创建了,其相应的磁盘空间并未被分配,
只有在有真正的数据插入进来时,才会被分配磁盘块。具体实验过程如下:
从时间对比,可以发现创建一个相同大小的临时表空间比永久表空间快很多。再看一下两者的大小:
空间上可以看到永久表空间确实像
SQL
语句一样占用了
500M
的空间,而临时表空间对应文件只占用了
1.1M,此空间为管理内容信息,当数据库需要用临时表空间的时候,临时文件会增加,操作系统会再分配磁盘空间。对于临时文件磁盘空间延后分配问题,存在一定的生产隐患,需要引起足够的重视。具体生产隐患在哪儿?比如为配合业务,在数据库创建了一个1G大小的临时表空间,语句执行完成提示表空间已创建,因为临时数据文件稀疏文件的特性,此时操作系统并未真正分配空间,也许该文件系统只有100M的剩余空间。那么这个时候恰巧一条特别占用临时表空间的语句要执行,当临时文件增长到100M的时候,已经无法再分配空间,导致语句执行失败,出现如下错误:点击(此处)折叠或打开
- 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
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01
因此临时数据文件创建后一定要在文件系统预留相关的空间,防止临时数据文件突增无法申请到磁盘空间。
临时表空间使用率
参考永久表空间使用率语句,临时表空间使用率的语句如下(注:以下语句仅适用于
11G
以上数据库包含
11g
):
对于
10g
数据库,可以使用如下语句监控,此语句对
11g
也使用
下面创建一个数据量较大的表
T
进行试验,具体过程如下:
重启打开一个会话,执行
SELECT * FROM t order by 1;再其他会话查看表空间的使用率
排序操作的确一直在使用临时表空间,随着语句的执行,使用率一直在增加。当操作完成后,再次查看临时表空间使用率为0%,说明系统已经自动回收。再翻过来看一下具体磁盘的占用情况:
临时文件磁盘空间一旦分配后不会被回收。
上述语句可以精确定位到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;
- TBS_NAME TOTAL(M) FREE(M) USED%
------------------------------ ----------------------------------------- ----------------------------------------- ----------------------
TEMP01 500M 499M .2%
点击(此处)折叠或打开
- 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%
点击(此处)折叠或打开
- SQL> SELECT count(*) FROM t;
- COUNT(*)
- ----------
- 8128000
点击(此处)折叠或打开
- 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%
点击(此处)折叠或打开
- 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
总之:
1.因为稀疏文件的特性,请确保临时表空间对应文件系统有相应的空间预留
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根本不会去恢复临时表空间。
或者
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博客 ” ,链接:http://blog.itpub.net/29827284/viewspace-2134742/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29827284/viewspace-2134742/