Oracle 删除数据后释放数据文件所占磁盘空间

小咪咪 2022-08-08 09:11 262阅读 0赞

测试的时候向数据库中插入了大量的数据，测试完成后删除了测试用户以及其全部数据，但是数据文件却没有缩小。经查阅资料之后发现这是 Oracle “高水位”所致，那么怎么把这些数据文件的大小降下来呢？解决办法如下：

概念：

表空间的相关知识请见[这里][Link 1]，详细的介绍了 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.登录 sqlplus：

> 语法：sqlplus username/password@hostname:port/sid
> 
> 例：sqlplus system/orcl@localhost:1521/orcl

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

>  
> 
> SQL> 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)的编号：(似乎这个最大编号可以代表该数据文件中数据块的数量，这一点有待考证。)

> SQL> select max(block\_id) from dba\_extents where file\_id=4;
> 
> MAX(BLOCK\_ID)  
> \-------------  
> 65673

3.计算该表空间实际占用的空间：

> \--查询数据块的大小，单位是 byte
> 
> SQL> show parameter db\_block\_size;
> 
> NAME TYPE VALUE  
> \------------------------------------ ----------- ------------------------------  
> db\_block\_size integer 8192
> 
> \--8192 byte = 8 kb
> 
> \--接下来计算该表空间占用的物理空间
> 
> SQL> select 65673 \* 8 / 1024 from dual;
> 
> 65673\*8/1024  
> \------------  
> 513.070313
> 
> \--实际占用的物理空间是 513MB 多点

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

> SQL> alter database datafile 'D:\\oracle\\product\\10.2.0\\oradata\\orcl\\USERS01.DBF' resize 600m;
> 
> 数据库已更改。

OK，数据文件从修改前的 1.4GB 变成了 600MB。对于其它的数据文件，大家也知道如何收缩了吧？

> 参考文献：
> 
> Oracle表空间（tablespaces）http://www.cnblogs.com/fnng/archive/2012/08/12/2634485.html
> 
> Oracle降低HWM的集中方法 http://hi.baidu.com/wschao2005/item/5383388fb2e21fc8b17154b1
> 
> 修改oracle数据文件大小 http://www.2cto.com/database/201204/126864.html
> 
> oracle删除数据后,空间不释放如何解决??? http://bbs.csdn.net/topics/310046810

[Link 1]: http://www.cnblogs.com/fnng/archive/2012/08/12/2634485.html