resize 文件大小收不回来啥原因

有时文件膨胀太大，或者对象清理后想调整数据文件大小，看看文件使用率不高：

col tablespace_name for a12
col file_name for a50
select b.file_id ,
       b.tablespace_name ,
       b.file_name ,
       round(b.bytes / 1024 / 1024) size_M,
       round(c.max_extents / 1024 / 1024) max_extents_M,
       round(b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024) total_M,
       trunc((b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024) used_M,
       trunc(sum(nvl(a.bytes, 0)) / 1024 / 1024) free_M,
       trunc(100-sum(nvl(a.bytes, 0)) / (b.bytes) * 100, 2) used_percent
  from dba_free_space a, dba_data_files b, dba_tablespaces c
 where a.file_id = b.file_id
   and b.tablespace_name = c.tablespace_name
 group by b.tablespace_name,
          b.file_name,
          b.file_id,
          b.bytes,
          c.max_extents,
          b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024
 order by b.tablespace_name;

可是 将文件resize 10M时，提示ORA-03297

是有对象存在文件的“末尾”，收缩时发现这中情况后就停止了。


怎么办？


MOS中有个脚本 show_segment_above_size.sql
用这个脚本可以查出哪些对象导致的，执行这个脚本：

输入文件号8和想要的大小10M，然后会看到是T41这个表导致的。

接下来的策略就是把T41 表move到其他表空间或者导出再导入，来改变位置，然后再resize应该就可以了。

查一下各文件中最大的extend位置（较慢）

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
/

这个结果就是每个文件可以收缩的最小值（最高水位）。

参考： 如何在通过查找表 Highwatermark 来调整数据文件大小时解决 ORA-03297（文档 ID 130866.1）