作者：兴霖先玲广_215 | 来源：互联网 | 2014-07-13 17:52
我们在136上的oracle数据库进行压力测试时，发现数据库的数据文件占用物理空间达到20多个G，但是在我们清掉各个表的数据后（truncate掉各个日志表），发现数据库实际数据占用的空间只有20多M，但物理文件仍然20多...SyntaxHighlighter.all();
我们在136上的oracle
数据库进行压力测试时，发现数据库的数据文件占用物理空间达到20多个G，但是在我们清掉各个表的数据后（truncate掉各个日志表），发现数据库实际数据占用的空间只有20多M，但物理文件仍然20多G。
经查资料，这个是oracle的表和表空间的“高水位”问题造成的，解决方案如下：
前提知识：
1. Oracle数据库中的物理存储空间是以块（segment）为单位的
2. 修改数据库表空间大小的语句：
ALTER DATABASE DATAFILE &＃39;D:\ORADATA\E
CSS20&＃39; RESIZE 206M
但是直接运行该语句的话会报如下错误：
Failed to commit: ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据
解决方案（以136上的ECSS20表空间为例）：
1. 查询oracle数据文件及其编号。SQL语句如下
select file#,name from v$datafile; www.2cto.com
查询出数据库的所有数据文件，其中包含如下，正是达到20多G的数据文件
FILE# NAME
------------------------------------------------------------------------------------------
6 D:\ORADATA\ECSS20
2. 查找该数据文件的最大块号。语句如下：
select max(block_id) from dba_extents where file_id=6;
查询结果如下：
MAX(BLOCK_ID)
-------------
534785
3. 计算该表空间目前实际占用的空间（不是物理文件的大小）
显示每个数据块的大小。语句如下：
show parameter db_block_size;
结果为8192,就是8K。
然后计算所有数据块占用的物理空间（拿计算器计算也一样）
select 534785*8/1024 from dual;
结果为4178.00781M，就是4G多
4. 然后我们知道目前用了4G多，我们就可以把数据文件大小Resize到4G多一点
ALTER DATABASE DATAFILE &＃39;D:\ORADATA\ECSS20&＃39; RESIZE 4200M;
数据库已更改。正常。
到此为止，实际数据文件的大小就由20多G到4G多了。
5. 继续往下走，因为我们实际数据占用了几十M，但数据文件还有4G多，还是我们把之前的表truncate掉后才能得到的。现在查一下占用最大块（segment 534785）的是什么。语句如下：
select distinct owner, segment_name, segment_type,tablespace_name from dba_extents where file_id =6 and block_id=534785; www.2cto.com
查到的结果如下：
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------------------------------------------------------------------------------
AJITA BIZTALKINGINFO TABLE ECSS
说明目前占用最大块的对象是表BIZTALKINGINFO
6. 把表挪动一下，把表从当前表空间转移到了另外一个表空间（要已经存在的），语句如下：
alter table biztalkinginfo move tablespace ECSS_LUCIFER;
再次查询物理文件中的最大块号（步骤2），本次查询结果为534761，结果已经变小了，再查询该块的数据时BizTalkingInfo的主键。
7. 分析可知，在我们数据表已经插入大量数据后，才建表BizTalkingInfo，然后该表占用的块就偏大。然后我们resize数据文件时就不能小于该块。最简单的办法是删掉该表相关的东西，然后重建即可。当然也有比较复杂的办法可以办到。
8. 有一个结论就是：建表一般要放在数据表初始化之前进行，最好不要再初始化了大量数据，尤其是日志数据后再建表。
作者 Ajita
京公网安备 11010802041100号