DBA生存技能：Oracle在线缩小数据文件全流程详解（附resize+coalesce最佳实践）
起因说明
某项目数据库的数据文件所在的文件系统使用率很高，但是实际检查表空间使用率很低，后经过确认是某表空间一次性扩容较大空间造成了文件系统空间的浪费。因此建议缩小datafile文件来降低文件系统使用率。
该操作可能带来安全风险，因此建议
- 确保操作系统有完整且有效的逻辑或者rman备份。
- 现在测试环境进行验证测试。
- 业务低峰期执行。
操作过程方案1（ 检查free space at end of file ）
上传脚本并赋予权限
下载resize.sql上传脚本到/oracle目录并赋予执行权限
链接: 提取码: mmf9
#chmod 755 /oracle/resize.sql
执行脚本
>sqlplus / as sysdba >@/oracle/resize.sql
运行脚本后，输出案例如下
缩小数据文件
根据输出的内容，复制数出来，然后根据实际需求来评估需要保留的空间
> alter database datafile '/rbdata/orcl/test02.dbf' resize 10m;
> alter database datafile '/rbdata/orcl/test01.dbf' resize 1088K;
操作过程方案2（检查高水位）
直接执行查询操作
>su - oracle
>sqlplus / as sysdba
> column file_name format a50; column tablespace_name format a15; column highwater format 9999999999; set pagesize 9999 select a.tablespace_name ,a.file_name ,(b.maximum+c.blocks-1)*d.db_block_size highwater from dba_data_files a ,(select file_id,max(block_id) maximum from dba_extents group by file_id) b ,dba_extents c ,(select value db_block_size from v$parameter where name='db_block_size') d where a.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum order by a.tablespace_name,a.file_name 输出结果如下：--备注 highwater 单位是字节
评估resize大小
检查datafile文件大小
select file_name,file_id ,bytes/1024/1024 MB from dba_data_files where tablespace_name in ('TEST');
当前3个datafile 大小都是100MB，从高水位可以看出来基本没有缩小的可能了。
当尝试truncate 一张测试表后，高水位下降了，如下
--备注 highwater 单位是字节