【实施方案】Oracle数据文件瘦身的正确姿势

前言

用户由于没有新增购买存储的打算，且数据库主机的数据文件所在目录水位线常年处于95%以上，故通知业务部门对不用的表进行清理，最后让笔者对数据文件的物理空间进行释放。换言之，需要笔者对数据文件进行缩减，从而减少数据文件在操作系统上的空间占比。

解决步骤

一 . 先查询下表空间的使用情况

确定哪些表空间值得缩减。涉及到核心业务表空间的话，最好略过，毕竟缩减的过程，可能会对表空间内的对象进行锁定，影响业务就不好了。

Select t.tablespace_name "tablespace name",
ROUND(a.bytes) "total size" ,
NVL(ROUND(b.bytes),0) "free size ",
'(' || TO_CHAR(ROUND(100*(NVL(b.bytes,0)/NVL(a.bytes,1))))
|| '%)' "free percent"
from dba_tablespaces t,
( select tablespace_name,
sum(bytes)/1024/1024 bytes
from dba_data_files
group by tablespace_name) a,
( select f.tablespace_name, sum(f.bytes)/1024/1024 bytes
from dba_free_space f
group by f.tablespace_name ) b
where t.status='ONLINE' and t.CONTENTS='PERMANENT' and t.tablespace_name = a.tablespace_name(+) and
t.tablespace_name = b.tablespace_name(+) ;
复制

二.查询表空间和数据文件的对应关系

select file_name,file_id,tablespace_name from dba_data_files;
复制

三.查询每个数据文件的水位线HWM（max_block代表数据文件的最高水位线）

SELECT file_id, file_name, blocks, max_block
FROM (SELECT file_id, MAX(block_id + blocks -1) max_block
FROM dba_extents
GROUP BY file_id)
JOIN dba_data_files USING (file_id);
复制

四.确定每个数据文件缩减后的最终值

注:确保resize后的尺寸略高于max_block

 (blocks*8/1024/1024-max_block*8/1024/1024)*1.1
复制

五.使用sql语句对数据文件进行缩减

ALTER DATABASE DATAFILE '/data1/wzdb/ttt.dbf' RESIZE 20G;
复制

如果未对结果乘以1.1的系数，则会收获以下报错。

六.校验

检查操作系统的文件目录是否成功缩减。

df -h
复制

后话

如果发现表空间有较大的空闲率，先别急着缩小数据文件，先查查水位线，用超出水位线一丢丢的空间，来resize数据文件，从而尽量避免数据丢失或者对象被锁定的状况。