Oracle 刪除資料後釋放資料檔案所佔磁碟空間

測試的時候向資料庫中插入了大量的資料，測試完成後刪除了測試使用者以及其全部資料，但是資料檔案卻沒有縮小。
經查閱資料之後發現這是 Oracle “高水位”所致，那麼怎麼把這些資料檔案的大小降下來呢？
解決辦法如下：

 

概念：

表空間的相關知識請見這裡http://www.cnblogs.com/fnng/archive/2012/08/12/2634485.html，詳細的介紹了 Oracle 資料庫的儲存結構。

 

高水位：
High Water Mark (HWM)，是段(Segment)的一個指標，界定了段(Segment)曾經配置過的 block 水位。

 

據說，隨著資料的 insert，所使用段(Segment)的資料塊(data block)也不斷增加，這時候高水位(HWM)也隨著上升。當資料被刪除後（無論是 delete 還是 truncate table）雖然被佔用的資料塊(data block)已經相應減少，但是高水位(HWM)並不會隨之下降。當高水位(HWM)下存在大量的空白資料塊(data block)時，如果發生全表掃描(Full Table Scan, FTS)就會造成很多額外的 IO。因為全表掃描(FTS)的時候讀取段(Segment)中的資料塊(data block)會一直讀取到高水位(HWM)才結束。高水位(HWM)就是段(Segment)中資料塊(data block)有沒有使用的分界線，所以全表掃描(FTS)所花費的時間不但不會因為資料的刪除而減少，反而會增加。(關於此段查詢效率的內容有待驗證，筆者未親自驗證。不過可以確定的是高水位確實不會隨著資料的刪除而下降。)

 

降低高水位的正確做法是先降低HWM，再確定實際佔有大小，再resize資料檔案。

 

資料檔案比較多，我們用其中一個較大的檔案做為 Demo，其它資料檔案如法炮製即可。我選擇的檔案是：D:\oracle\product\10.2.0\oradata\orcl\USERS01.DBF 1.4GB 左右。

 

1.登入 sqlplus：

語法：sqlplus username/password@hostname:port/sid

例：sqlplus system/orcl@localhost:1521/orcl

2.查詢這個資料檔案的編號：

SQL> select file#, name from v$datafile;

FILE# NAME

------------------------------------------------------------------------------------------

1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF

2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF

3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF

4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF

 可以看到，我們要操作的資料檔案的編號是4。

 

2.根據檔案 ID 查詢這個資料檔案最大資料塊(data block)的編號：(似乎這個最大編號可以代表該資料檔案中資料塊的數量，這一點有待考證。)

SQL> select max(block_id) from dba_extents where file_id=4; 

MAX(BLOCK_ID)

-------------

65673

 

3.計算該表空間實際佔用的空間：

--查詢資料塊的大小，單位是 byte

SQL> show parameter db_block_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192

--8192 byte = 8 kb

--接下來計算該表空間佔用的物理空間

SQL> select 65673 * 8 / 1024 from dual;

65673*8/1024

------------

513.070313

--實際佔用的物理空間是 513MB 多點

 

4.最後一步，把我們的資料檔案尺寸修改得比這個表空間實際佔用的物理空間大點就行了：

SQL> alter database datafile 'D:\oracle\product\10.2.0\oradata\orcl\USERS01.DBF' resize 600m;

資料庫已更改。

OK，資料檔案從修改前的 1.4GB 變成了 600MB。對於其它的資料檔案，大家也知道如何收縮了吧？