数据库在使用一段时间后,尤其是存在频繁的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#;
参考文档: