【Oracle】80.处理临时表空间满的利器-shrink

前言。
数据库在使用一段时间后，尤其是存在频繁的imp/exp操作以及排序操作时，
临时表空间就会越来越大，对应的数据文件也会很大。

从Oracle 11g开始，可以使用Shrink命令收缩临时表空间和临时文件，
也可以使用Resize命令对数据文件进行大小进行调整。

1.查看临时表空间数据文件大小。

set line 200
col file_name for a50
col tablespace_name for a30
col status for a10
col FILE_NAME for a80
select /*+ rule */ FILE_NAME,tablespace_name,status,round(bytes/1024/1024/1024,2) CUR_GB,AUTOEXTENSIBLE AUTOEXT,round(MAXBYTES/1024/1024/1024,2) MAX_GB
from dba_temp_files where tablespace_name in ('TEMP')
and round(bytes/1024/1024/1024,2)<>0;

或者:
col "Tempfile name" for a80
col TABLESPACE_NAME for a10
set lin 200
Select distinct f.tablespace_name,
       d.file_name "Tempfile name",
       round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
       round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024,
             2) "Free MB",
       round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB",
       round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) /
             round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100,
             2) as"Used_Rate(%)"
  from SYS.V_$TEMP_SPACE_HEADER f,
       DBA_TEMP_FILES           d,
       SYS.V_$TEMP_EXTENT_POOL  p
 where f.tablespace_name(+) = d.tablespace_name
   and f.file_id(+) = d.file_id
   and p.file_id(+) = d.file_id;

2.方法一:指定临时文件的名称收缩临时文件:效果非常好。

ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.1344.1159522425';

3.方法二:直接收缩临时表空间:效果非常好。

ALTER TABLESPACE TEMP SHRINK SPACE;


收缩后的结果,可以看到快速收缩。
FILE_NAME									 TABLESPACE_NAME		STATUS	       CUR_GB AUT     MAX_GB
-------------------------------------------------------------------------------- ------------------------------ ---------- ---------- --- ----------
+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.1344.1159522425	 TEMP				ONLINE		   .1 YES	  32
+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.1345.1159522427	 TEMP				ONLINE		   .1 YES	  32
+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.364.1082573511	 TEMP				ONLINE		    0 YES	  32
+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.367.1082573511	 TEMP				ONLINE		  .25 YES	  32
+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.371.1082573509	 TEMP				ONLINE		  .25 YES	  32
+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.713.1113644035	 TEMP				ONLINE		   .1 YES	  32
+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.714.1113644037	 TEMP				ONLINE		   .1 YES	  32
+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.715.1113644037	 TEMP				ONLINE		   .1 YES	  32
+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.716.1113644041	 TEMP				ONLINE		   .1 YES	  32

4.方法三:修改临时文件大小:效果不好,直接夯死。
 

alter database tempfile '+DATAC1/NEWDMIS/BF6F3B3E21116A47E0531405010A9419/TEMPFILE/temp.1344.1159522425' resize 30g;

5.总结 

建议使用如下两种方法搜索临时表空间。
ALTER TABLESPACE TEMP SHRINK TEMPFILE '../temp.1344.1159522425';
ALTER TABLESPACE TEMP SHRINK SPACE;