继上次发现某数据库表空间异常增长，然后通过一些手段找出问题之后（详见文章：记一次对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命令。
后记：应大家的要求，我将本文中用到的脚本上传了上来，大家可以免费下载，下载地址：本文脚本下载
