客户这边有一个需求：某个业务系统的数据库数据文件占用空间过大，磁盘空间资源紧张且不允许扩容，业务已经尽可能的做了数据清理，目前DBA想对数据文件尽可能的resize到允许值以释放部分空间，我通过互联网搜索到一个SQL，原版存在些排版格式的错误，我这里实际测试验证结合实际需求，最终修正如下，下面SQL可直接执行，只需按提示输入你要查询的表空间：
col tablespace_name for a30
set lines 140
select
a.tablespace_name,
a.file_id,
ceil((nvl(b.hwm, 1) * c.value) / 1024 / 1024) "smallest(mb) - hwm",
ceil(blocks * c.value / 1024 / 1024) "currsize(mb)",
ceil(blocks * c.value / 1024 / 1024) - ceil((nvl(b.hwm, 1) * c.value) / 1024 / 1024) "savings(mb)"
from dba_data_files a,
(select file_id, max(block_id + blocks - 1) hwm from dba_extents group by file_id) b,
(select value from v$parameter where name = ‘db_block_size‘) c
where a.file_id = b.file_id(+)
and a.status <> ‘INVALID‘
and a.tablespace_name = upper(‘&tablespace_name‘)
order by 2;
之前也说过很多次，作为一名合格的DBA，不应该盲目相信网络搜索来的信息（包括本文），一定要自己理解清楚后，才可以比较放心的使用。比如先搞清楚上面这条SQL的思路：
ceil()函数，实现向上取整，因为高水位要用于后续resize参考，所以选择向上取整；
nvl(b.hwm, 1)函数，如果b.hwm值为null，则用指定值1代替null值，这样即使存在没有被使用过的表空间，计算节省空间时也会有相对比较准确的值；
a.status<> ‘INVALID‘, 官方文档有解释，File status: AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace that was dropped)
而这个语句最核心的部分是：在dba_extents中找到 max(block_id + blocks - 1)，然后按file_id分组，这样就可以定位每个数据文件中有数据的部分高水位线是多少，再结合实际块大小算出具体数值。
比如在我自己的测试环境下执行，查询表空间DBS_D_JINGYU的数据文件情况：
Enter value for tablespace_name: DBS_D_JINGYU
old 12: and a.tablespace_name = upper(‘&tablespace_name‘)
new 12: and a.tablespace_name = upper(‘DBS_D_JINGYU‘)
TABLESPACE_NAME FILE_ID smallest(mb) - hwm currsize(mb) savings(mb)
------------------------------ ---------- ------------------ ------------ -----------
DBS_D_JINGYU 6 21 50 29
DBS_D_JINGYU 8 1 100 99
验证下这个值是否准确，比如拿6号数据文件举例：
sys@CRMDB> alter database datafile 6 resize 21M;
Database altered.
sys@CRMDB> alter database datafile 6 resize 20M;
alter database datafile 6 resize 20M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
可以看到还是非常靠谱的，我们可以根据这个来做参考，使用resize进行某些情况下的空间回收。
8号数据文件就是之前说的比较特殊的例子，实际是没有被使用的，用1代替了空值，那么是否可以将还没使用过的数据文件缩小到1M呢？我们来试下：
sys@CRMDB> alter database datafile 8 resize 1M;
alter database datafile 8 resize 1M
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
sys@CRMDB> alter database datafile 8 resize 2M;
Database altered.
实际测试发现1M是不可以的，原因是1M比最小值还要小，而resize到2M就可以成功。如果你好奇到底最小是多大，可以继续用二分法去验证，但这个实际的意义并不大。
最后把dba_data_files和dba_extents两个视图的字段贴出来参考：
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> desc dba_extents
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
如果对这类基本视图的某些字段还不熟悉，可以去多看看官方文档，说不定哪天就可以帮助到你实现某个需求。
小结：看起来很简单的一个需求，其实也考验到对Oracle体系原理的理解，如果之前只知道人云亦云的去复述Oracle的段、区、块什么的，一遇到这类简单需求却联想不出可能的解决方案，就需要反思下自己是否学习方法上存在问题了。
原文：https://www.cnblogs.com/jyzhao/p/13160487.html