繼上次發現某數據庫表空間異常增長，然后通過一些手段找出問題之后（詳見文章：記一次對oracle數據庫表空間異常增長的分析和處理）。我發現很多人對高水位的理解也不是很清晰，因此本文深入分析一下表產生高水位的原因，高水位下空塊情況，以及為什么收縮表之后仍然在dba_tables中仍然能查到不少空塊的原因。
一、清除表的高水位線
1）通過exp/imp和expdp/impdp方式清除表的高水位線
實測通過exp/imp和expdp/impdp方式并不一定能清除表的高水位線。
這個大家可以自己做下實驗，我測試了幾種情況，有些表能降低高水位，有些表不能清除表的高水位。如下測試時沒有清除掉高水位：
實驗用到的表，我已經使用exp導出來了，歡迎大家下載測試！下載地址：high_wmk_1.dmp
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
HIGH_WMK_1 TABLE
SQL> analyze table HIGH_WMK_1 compute statistics;
Table analyzed.
SQL> select segment_name,partition_name,round(bytes/1024/1024,2) size_mb from user_segments where segment_name='HIGH_WMK_1';
SEGMENT_NAME PARTITION_NAME SIZE_MB
------------------- ------------------- ----------
HIGH_WMK_1 88
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='HIGH_WMK_1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
-------------- ---------- ---------- ------------
HIGH_WMK_1 1 222 11042
exp導出：
$ exp test/test file=exp_high_wmk_1.dmp log=exp_high_wmk_1.log statistics=none tables=high_wmk_1
Export: Release 11.2.0.3.0 - Production on Mon Apr 25 07:48:28 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table HIGH_WMK_1 1 rows exported
Export terminated successfully without warnings.
先進入test用戶下收縮原表高水位，查看收縮情況并刪除原表：
SQL> alter table high_wmk_1 enable row movement;
Table altered.
SQL> alter table high_wmk_1 shrink space;
Table altered.
SQL> analyze table HIGH_WMK_1 compute statistics;
Table analyzed.
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='HIGH_WMK_1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
HIGH_WMK_1 1 21 35
SQL> drop table HIGH_WMK_1 purge;
Table dropped.
然后使用imp重新導入：
$ imp test/test file=exp_high_wmk_1.dmp log=imp_high_wmk_1.log full=y
Import: Release 11.2.0.3.0 - Production on Mon Apr 25 07:56:03 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. importing TEST's objects into TEST
. . importing table "HIGH_WMK_1" 1 rows imported
Import terminated successfully without warnings.
$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 25 07:56:24 2022
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--收集統計信息
SQL> analyze table HIGH_WMK_1 compute statistics;
Table analyzed.
--查看空塊數量
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='HIGH_WMK_1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
HIGH_WMK_1 1 222 11042
SQL> select count(*) from HIGH_WMK_1;
COUNT(*)
----------
1
可見，通過exp/imp方式并沒有清除表的高水位線。
2）通過移動表空間方式降低高水位
注：在操作之前，先對有高水位的表做一次exp導出，為之后的實驗做準備。
SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name='CHONGZHI_LIST';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
SQL> analyze table chongzhi_list compute statistics;
Table analyzed.
--查看當前表中是否有高水位
SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name='CHONGZHI_LIST';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
1722 222 19234
從查詢中可知，該表有19234個空塊，也就是肯定有高水位。
--查詢表實際有數據的塊共有多少個
SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)||'-'||dbms_rowid.rowid_relative_fno (rowid)) "used blocks" from CHONGZHI_LIST;
used blocks
-----------
40
SQL> alter table CHONGZHI_LIST enable row movement;
Table altered.
SQL> alter table CHONGZHI_LIST shrink space;
Table altered.
注：如果加上cascade數據，則會連同index占用的空間也一起收縮 ALTER TABLE user_name.table_name SHRINK SPACE CASCADE;
SQL> alter table CHONGZHI_LIST disable row movement;
Table altered.
SQL> analyze table chongzhi_list compute statistics;
Table analyzed.
SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name='CHONGZHI_LIST';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
1722 41 39
收縮后，查詢到EMPTY_BLOCKS已經降下來了。
--查詢指定的表的數據實際占用了多少個塊
SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)||'-'||dbms_rowid.rowid_relative_fno (rowid)) "used blocks" from CHONGZHI_LIST;
used blocks
-----------
40
可見，實際存儲數據的塊共有40個，為什么dba_tables中顯示blocks占用了41個塊呢？因為還有一個塊存儲了段頭信息，所以是40+1=41。
那既然做了收縮，為什么EMPTY_BLOCKS仍然有39個塊呢，雖然已經從19234降至39，但為什么還會剩下39個空塊未使用呢？這些空塊不能被收縮了嗎？
經過下面的一系列實驗發現，原來這里顯示的39個空塊并不全是空塊，其中大部分已經被該表對應的索引使用掉了。
二、分析表中高水位下塊的分布
重新imp導入有高水位的表,分析shrink前后變化：
2.1 有高水位的情況下
在這里可以看到，該表總共有19456個塊，未使用的塊有19200個。該表目前占用了152MB的空間，而其中有150MB的空間都是空閑的。
–查塊分布
從查詢中可看出，其中有182個塊的空間空閑率為75%-100%，而這些塊又可能分布在不同的區和段中，導致該表占用了大量空閑空間。其中full塊有39個。
注意：
如果有太多的fs1、fs2和fs3塊(大部分是fs1和fs2塊)，則碎片化被認為是高的，因為這些塊可能不允許插入，當新的插入出現時，空閑空間和段可能需要擴展。
從空間回收的角度來看，如果有太多的fs3、fs4塊(特別是fs4塊)，并且將來插入的可能性很小，那么重新組織表（shrink）將釋放大量空間。重新組織表會壓縮這些塊，從而增加FULL塊，減少fs1、fs2、fs3和fs4塊，從而減少塊的總數。
–對表進行收縮
–再次分析塊的占用情況
此時發現，只有1個塊比較空閑了。
這里得到的結果是總共占用了80個塊，其中只有5個塊是空塊。那為什么在dba_tables中查出來的空塊數量為39個呢？
因為該表中還有索引也占用了一些塊，通過查詢其中一個索引，發現其使用了16個塊，有3個塊是空閑的。
三、得出結論
1）我們從dba_tables中的EMPTY_BLOCKS字段看到的值，并不代表全是空塊數量。其中還有被索引等占用的塊在里面。
2）使用shrink space能夠有效降低表的高水位。如果要收縮臨時表空間，可以使用alter tablespace temp shrink space命令。
后記：應大家的要求，我將本文中用到的腳本上傳了上來，大家可以免費下載，下載地址：本文腳本下載
