在数据库中对于数据文件都是提前规划,不够就加的情况,很少会留意到其实有些数据文件那么大,其实条件允许也是可以收缩收缩的。
这种情况在本地测试环境中尤为突出,本来就用虚拟机跑个数据库,硬盘空间就够紧张,几十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,这是多么大的改变,这种收缩文件带来的实惠你值得拥有。