Oracle 释放（恢复）数据文件大小

今天在测试数据量占用磁盘空间大小的时候遇到问题，当插入大量数据进入数据库后，数据文件变大，但是删除以后，数据文件大小不变，这里有解决办法。

经查阅资料之后发现这是 Oracle “高水位”所致，那么怎么把这些数据文件的大小降下来呢？

概念：

表空间的相关知识请见这里http://www.cnblogs.com/fnng/archive/2012/08/12/2634485.html，详细的介绍了 Oracle 数据库的存储结构。

高水位：
High Water Mark (HWM)，是段(Segment)的一个指标，界定了段(Segment)曾经配置过的 block 水位。

据说，随着数据的 insert，所使用段(Segment)的数据块(data block)也不断增加，这时候高水位(HWM)也随着上升。当数据被删除后（无论是 delete 还是 truncate table）虽然被占用的数据块(data block)已经相应减少，但是高水位(HWM)并不会随之下降。当高水位(HWM)下存在大量的空白数据块(data block)时，如果发生全表扫描(Full Table Scan, FTS)就会造成很多额外的 IO。因为全表扫描(FTS)的时候读取段(Segment)中的数据块(data block)会一直读取到高水位(HWM)才结束。高水位(HWM)就是段(Segment)中数据块(data block)有没有使用的分界线，所以全表扫描(FTS)所花费的时间不但不会因为数据的删除而减少，反而会增加。(关于此段查询效率的内容有待验证，笔者未亲自验证。不过可以确定的是高水位确实不会随着数据的删除而下降。)

降低高水位的正确做法是先降低HWM，再确定实际占有大小，再resize数据文件。

数据文件比较多，我们用其中一个较大的文件做为 Demo，其它数据文件如法炮制即可。我选择的文件是：D:\oracle\product\10.2.0\oradata\orcl\USERS01.DBF 1.4GB 左右。

1.查询这个数据文件的编号：

select file#, name from v$datafile;

FILE# NAME

——————————————————————————————

1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

 可以看到，我们要操作的数据文件的编号是4。

2.根据文件 ID 查询这个数据文件最大数据块(data block)的编号：

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

MAX(BLOCK_ID)

————-

65673

3.接下来计算该表空间占用的物理空间

select 65673 * 8 / 1024 from dual;

65673*8/1024

————

513.070313

4.最后，把我们的数据文件尺寸修改得比这个表空间实际占用的物理空间大点就行了：

SQL> alter database datafile ‘D:\oracle\product\10.2.0\oradata\orcl\USERS01.DBF’ resize 600m;

数据库已更改。