在資料庫中對於資料檔案都是提前規劃，不夠就加的情況，很少會留意到其實有些資料檔案那麼大，其實條件允許也是可以收縮收縮的。
這種情況在本地測試環境中尤為突出，本來就用虛擬機器跑個資料庫，硬碟空間就夠緊張，幾十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,這是多麼大的改變，這種收縮檔案帶來的實惠你值得擁有。

關於收縮資料檔案的嘗試

相關文章