關於收縮資料檔案的嘗試

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