在很多时候，由于数据库或应用的异常，可能导致临时表空间过度扩展，占用极大空间，此时就需要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中被修正。
我们始终应当怀有的经验是：在任何工作中的繁忙数据库上，执行任何操作都应当非常谨慎。
