暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

临时表空间过度扩展的问题解决

eygle 2019-10-17
1832

在很多时候,由于数据库或应用的异常,可能导致临时表空间过度扩展,占用极大空间,此时就需要DBA介入进行处理。通常这种问题是由于应用过度排序导致的,一个错误的SQL编写或笛卡尔积都可能使得临时表空间过度扩展。以下就是这样一个案例。


这是一个用户生产数据库,因为空间警报而进行数据库检查,检查时发现临时表空间已经扩展到约32G的空间:

[root@stat datafile]# ll
-rw-r-----  1 oracle oinstall  1593843712 Dec 18 17:31 o1_mf_system_29448mn7_.dbf
-rw-r-----  1 oracle oinstall 34358697984 Dec 18 17:32 o1_mf_temp_2944959v_.tmp
-rw-r-----  1 oracle oinstall  3334479872 Dec 18 17:31 o1_mf_undotbs1_29448ypl_.dbf
……


此时我们可以考虑通过resize的方式减少文件大小,释放空间:

[oracle@stat ~]$ sqlplus '/ as sysdba'
 
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Dec 18 17:33:21 2006
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
 
SQL> select name,bytes/1024/1024/1024 from v$tempfile;
NAME                                                BYTES/1024/1024/1024
----------------------------------                    -------------------
/data2/oradata/STAT/datafile/o1_mf_temp_2944959v_.tmp      31.9990234

 

而此时可以resize临时文件到多大的大小呢?


可以查询一下临时空间使用情况来确认一下,v$sort_usage 这个视图记录了排序空间的使用情况,我们可以找到最大的块号:

SQL> select max(segblk#) from v$sort_usage;
MAX(SEGBLK#)
------------
     2964105
SQL> select 2964105*8192/1024/1024/1024 from dual;
2964105*8192/1024/1024/1024
---------------------------
                 22.6143265


可以按照这个结果将临时表空间收缩到23G左右:

SQL> alter database tempfile '/data2/oradata/STAT/datafile/o1_mf_temp_2944959v_.tmp' resize 23G;
 
Database altered.

 

查询v$sort_usage我们可以找到那些正在使用临时表空间的用户及空间信息:

SQL> select username,sqlhash,segfile#,segtype,segblk# from v$sort_usage;
 
USERNAME                          SQLHASH   SEGFILE# SEGTYPE      SEGBLK#
------------------------------ ---------- ---------- --------- ----------
VASCMS                         1483226149        201 DATA         2963849
VASCMS                         1483226149        201 LOB_DATA     2963977
VASCMS                         1483226149        201 INDEX        2964105
VASCMS                         1483226149        201 LOB_DATA     2957705


如果等待这些用户完成了排序操作,释放了空间,则可以回收更多的空间。

SQL> select username,segblk#,blocks from v$sort_usage;
no rows selected
 
SQL> alter database tempfile  '/data2/oradata/STAT/datafile/o1_mf_temp_2944959v_.tmp' resize 10240M;
 
Database altered.
 
SQL> select name,bytes/1024/1024/1024 from v$tempfile;
NAME                                               BYTES/1024/1024/1024
------------------------------------------------       ------------------
/data2/oradata/STAT/datafile/o1_mf_temp_2944959v_.tmp   10

 

很多时候临时空间不能及时释放,我们就需要通过前文介绍的方法切换临时表空间来进行问题解决。


在Oracle Database 11g中,一个新的视图被用来记录临时表空间的使用情况,这个视图是DBA_TEMP_FREE_SPACE,通过查询该视图可以获得临时表空间的空间分配、使用大小、剩余空间等信息:

SQL> select * from dba_temp_free_space;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------------------- --------------- --------------- ----------
TEMP                              122683392       122683392         121634816


类似的查询临时表空间剩余空间的另外一个视图是 V$TEMP_SPACE_HEADER

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME                BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP                              1114112          0

 

现在可以直接针对临时表空间或临时表空间文件进行Shrink空间收缩:

SQL> alter tablespace temp shrink space keep 100M;
Tablespace altered.
 
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                 105906176         1048576  104857600
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/data1/oradata/phsdb/temp01.dbf
 
SQL> alter tablespace temp shrink tempfile '/data1/oradata/phsdb/temp01.dbf' keep 80M;
Tablespace altered.
 
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                  83951616           65536   83886080


如果不指定保留空间大小,Shrink将会收缩表空间到最小的可能空间大小:

SQL> alter tablespace temp shrink space;
Tablespace altered.
 
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                   1114112           65536    1048576


这一功能增强简化了临时表空间的管理和维护。虽然Shrink功能带来了临时表空间管理的简化,但是在临时表空间繁忙使用期间执行Shrink操作,可能会遇到ORA-00600 [kcbrls_1]错误,Bug 8650391与此有关,这一Bug在Oracle 11.2中被修正。


我们始终应当怀有的经验是:在任何工作中的繁忙数据库上,执行任何操作都应当非常谨慎。

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论