分区表或分区索引的BLOCK_ID很大导致DATAFILE无法RESIZE的解决方法

一 前言

最近发现磁盘空间有3T的性能机器出现了磁盘空间不足的现象，该机器主要部署ORACLE数据库，所以，猜测很可能是表空间数据文件变更导致的。接下来，就需要一步步的排查问题了，最终确认是ORACLE BLOCK_ID惹的祸。

二 定位磁盘空间占用情况

首先需要确定是哪些文件占用空间，使用du -sh * ，果不其然，有个表空间增加了20个数据文件，而且每个数据文件设置30G，Word天，谁这么狠，居然找不到元凶，好吧，那我就任意处置了，不能影响后面的性能测试。

三 删除数据文件

既然发现这么多数据文件，当然想直接drop掉，于是，不以为然的执行alter tablespace TEST drop datafile '/oradata/dat20.dbf';先把最后一个干掉，结果执行报错“ORA-03262: THE FILE IS NON-EMPTY”，呵，居然有数据，直接删不掉。于是，就想查询这个表空间的表，把数据TRUNCATE掉，但又考虑到该表空间TABLE就有上千张，而且不能确定哪张表可删，不能太鲁莽，事实证明，真和数据无关。

四 退而求其次-RESIZE 数据文件释放空间

既然不能drop 数据文件，那就resize它，就不信拿不回空间。于是，先查下可以释放多少空间出来，先执行如下命令：

select d.file_name,d.file_id,d.bytes/1024/1024 as d_byte,sum(f.bytes/1024/1024) as free_byte 

from dba_data_files d,dba_free_space f 

where d.file_id=f.file_id and d.file_id=67 

group by d.file_name,d.file_id,d.bytes/1024/1024;

输出显示67号数据文件可用空间29.9G，看到这里，心里暗骂，是谁这么不靠谱，乱加乱设数据文件。不过，都是小问题，resize成1G就行了。于是，又兴冲冲的赶紧执行ALTER DATABASE DATAFILE '/oradata/hisdat20.dbf' RESIZE 1G; 居然又报错了，

“ORA-03297:file contains used data beyond requested RESIZE value”，看到这个报错，开始意识到可能问题没有这么简单。

五 shrink space降低高水位

既然实际数据很少，resize却不能成功，就表明是某些数据块位于数据文件的末端，那就先降降HWM高水位，对表空间的表进行操作，主要命令如下：

alter table test_table enable row movement;

alter table test_table shrink space; ---降低高水位，释放空间

alter table test_table disable row movement;

当然，这样一个个的执行不显示，需要批量执行，命令如下：

SELECT DISTINCT 'alter table ' || segment_name || ' enable row movement;'||

                'alter table ' || segment_name || ' shrink space;'||

                'alter table ' || segment_name || ' disable row movement;'

 FROM dba_extents

 WHERE tablespace_name = 'TEST'

 AND segment_type = 'TABLE'

降低HWM后，再次执行RESIZE操作，报错依旧，好吧，既然这样都没搞定，需要认真研究下了。

六 找到真凶和解决方法

通过上述尝试，发现数据文件可用空间充足，但对ORACLE而言，数据文件使用了30G，所以RESIZE到1G会报错失败，尽快进行了降高水位或TRUNCATE操作都无济于事。于是，排查和解决思路是这样的：

1）查询数据文件的最大BLOCK_ID

select max(block_id) from dba_extents where file_id=67;

2）确定该BLOCK_ID与哪个表或索引有关

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME FROM DBA_EXTENTS A WHERE FILE_ID = 67 AND block_id = 3839929;

查询后，发现最大的BLOCK_ID都是与分区表或分区索引有关的。

3）针对最大BLOCK_ID出现在分区表的处理方法

对分区表出现最大BLOCK_ID的情况，采用先降分区表高水位，然后MOVE表空间，命令如下：

alter table TEST_TABLE MODIFY PARTITION P101101 shrink space;---注意降低高水位并不能降低数据文件中block_id大小

alter table TEST_TABLE move partition P101101 tablespace TEST;---move操作数据移动表空间最前面的空闲block，注意需要重建索引

4）针对最大BLOCK_ID出现在索引分区的处理方法

对索引分区出现最大BLOCK_ID的情况，重建分区索引即可，命令如下：

ALTER INDEX IDX_TEST_TABLE REBUILD PARTITION P201201

5）处理完后，再次执行RESIZE操作，数据文件大小修改成功。

最后，因为同个文件号上可能出现多个分区表，分区索引需要处理的情况，建议像第五步写成批量执行，提高效率。

关于shrink space降低高水位，可以参考博文Oracle delete操作隐藏着你可能不知道的秘密