在数据库中对于数据文件都是提前规划，不够就加的情况，很少会留意到其实有些数据文件那么大，其实条件允许也是可以收缩收缩的。
这种情况在本地测试环境中尤为突出，本来就用虚拟机跑个数据库，硬盘空间就够紧张，几十M几百M都是空间，都得“兆兆”计较。
今天在做dataguard的练习的时候，发现主库中的数据文件有些大，差不多4G左右，其实这个库里也没有装什么特别的东西，都是些测试表，完全可以清楚，使用dba_segments查看了下，有一个测试表在2G左右，占了不少的空间，基本一个数据文件都占完了。
FILE_NAME BYTES
----------------------------------------------------------------------------------------
/u02/ora11g/oradata/TEST11G/users01.dbf 2187329536
看来清除以后能节省不少的空间，就使用drop table xxxx purge;给清理了，但是发现数据文件的大小还是丝毫没有改变。
我使用下面的语句简单验证了一下,表空间USER占用的情况在300M左右。
> select sum(bytes) --,segment_name
from dba_segments
where tablespace_name='USERS';
SUM(BYTES)--,SEGMENT_NAME
-------------------------
31129600
为了尽可能多的释放更多空间，我又删了几个分区表,感觉应该剩下不少空间了。
就使用如下的语句来生成resize语句来收缩一下数据文件（我建的这个表空间只有一个数据文件）
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
生成的语句如下：
alter database datafile '/u02/ora11g/oradata/TEST11G/users01.dbf' resize 2076M;
可以看到基本没有任何改变，但是根据我的直观感觉，确实没有多少表了，空间也确实都腾出来了。
可以简单的验证一下,数据文件是4号，使用dba_extents可以看到占用的空间情况和对应的块的情况。
> select file_id,max(block_id+blocks-1) HWM,block_id
from dba_extents
where file_id=4
group by file_id,block_id;
FILE_ID HWM BLOCK_ID
---------- ---------- ----------
4 447 440
4 255 248
4 543 536
4 159 152
4 415 408
4 479 472
4 463 456
4 495 488
4 679 672
4 263871 263864
4 151 144
4 455 448
4 623 616
4 631 624
4 535 528
4 551 544
4 895 768
4 263847 263840
4 191 184
4 311 304
4 327 320
4 527 520
4 399 392
4 407 400
4 431 424
4 567 560
4 591 584
4 639 632
4 265639 265632
4 265647 265640
4 239 232
4 247 240
4 303 296
4 511 504
4 519 512
4 703 696
4 167 160
4 559 552
4 599 592
4 265655 265648
......
标黄的部分都是空间占用差别比较大的。我们来在这个基础上做一个简单的分析。
首先得到4号数据文件中，块号最大的数据块block_id
> SELECT MAX(block_id)
FROM dba_extents
WHERE tablespace_name = 'USERS';
MAX(BLOCK_ID)
-------------
265648
然后简单换算一下，可以得到“对应”的数据文件是2G左右，这个就和最开始碰到的情况吻合了。
> SELECT 265648*8192/1024/1024 FROM dual;
265648*8192/1024/1024
---------------------
2075.375
看看这个数据块所在的extent对应的segment信息。
> select segment_name,owner from dba_extents where block_id=265648;
SEGMENT_NAME OWNER
--------------------------------------------------------------------------------- ------------------------------
TEST_ACCOUNT N1
可以看到对应的段是一个表，TEST_ACCOUNT
直接做一个move操作看看有没有立竿见影的效果。
> alter tableTEST_ACCOUNT move tablespace example;
Table altered.
但是重新生成resize字句，没有任何变化，还是2G左右。
不能这么被动的处理问题，直接生成了相关的信息。
> select owner,segment_name,segment_type,file_id,max(block_id+blocks-1) HWM,block_id
from dba_extents
where file_id=4
and block_id > 20000
group by owner,segment_name,segment_type,file_id,block_id;
OWNER SEGMENT_NAME SEGMENT_TYPE FILE_ID HWM BLOCK_ID
------ ------------------------- ----------------------- ---------- ----------
N1 TEST_CONSISTENT_GET TABLE 4 263831 263824
N1 IDX_TEST_CG INDEX 4 263871 263864
N1 IDX_TEST_CG INDEX 4 263863 263856
N1 TEST_CONSISTENT_GET TABLE 4 263839 263832
N1 TEST_CONSISTENT_GET TABLE 4 263847 263840
N1 TEST_CONSISTENT_GET TABLE 4 263855 263848
对于表直接使用move操作
>alter table TEST_CONSISTENT_GET move tablespace example;
对于索引直接使用rebuild操作
> alter index IDX_TEST_CG rebuild tablespace example;
这样就把它们给转出去了。这个时候再来看看空间的情况。
> SELECT MAX(block_id)
FROM dba_extents
WHERE tablespace_name = 'USERS';
MAX(BLOCK_ID)
-------------
768
> !ls -lrt user*.dbf
-rw-r----- 1 ora11g dba 7348224 May 12 15:07 users01.dbf
生成的resize语句如下：
alter database datafile '/u02/ora11g/oradata/TEST11G/users01.dbf' resize 7M;
从2G到7M,这是多么大的改变，这种收缩文件带来的实惠你值得拥有。