Oracle收缩临时表空间与临时文件

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

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

一、Shrink命令

Oracle帮助文档：Alter and Maintaining Tabelspaces

ALTER TABLESPACE SHRINK SPACE

ALTER TABLESPACE SHRINK TEMPFILE

 Shrink命令可使用keep选项指定收缩的下限。不指定keep选项，表空间或数据文件将被压缩到最小。

二、Resize命令

Oracle帮助文档：Changing Datafile Size

ALTER DATABASE TEMPFILE RESIZE

 当然，缩小和调整的下限，不能小于数据真实使用的大小。

三、查询临时表空间与数据文件的使用量

1、查询数据文件使用量

Select 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、查询查看表空间的使用量（dba_temp_free_space视图）

 其中tablespace_size 表示表空间的总大小（以字节为单位）

 allocated_space 表示已分配的总空间（以字节为单位），包括正在使用空间以及可重用空间

 free_space 表示可用的总空间（以字节为单位），包括已分配的、可重用的以及未分配的空间

四、查询并生成resize语句

 可以使用下面的语句查询并生成resize语句

select a.file#,
       a.name,
       a.bytes / 1024 / 1024 CurrentMB,
       ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
       (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
       'alter database datafile ''' || a.name || ''' resize ' ||
       ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD
  from v$datafile a,
       (select file_id, max(block_id + blocks - 1) HWM
          from dba_extents
         group by file_id) b
 where a.file# = b.file_id(+)
   and (a.bytes - HWM * block_size) > 0
 order by file#;

参考文档：

Oracle table shrink space commands

Using the Oracle Shrink Command