来源：ORACLE数据库高水位线(high water mark) - 墨天轮 (modb.pro)
文档课题：ORACLE数据库高水位线(high water mark).
1、相关概念
ORACLE数据库逻辑结构包括：数据库块(block)，区(extent)，段(segment)，表空间(tablespace).高水位线存在于段中，用于标识段中已使用过的数据块与未使用过的数据块二者间交界.扫描表数据时，高水位线以下的所有数据块都必须被扫描.
高水位线存在于段，且位置记录在段头，也就是段的第一个数据块中.因此可以转储段头信息来看高水位线信息.段又分数据段、索引段、临时段、回滚段等.当创建段的时候会分配区，区是由若干个物理连续的数据块组成.区的分配是需要初始化数据块的，默认初始化单位为1M.注意：高水位线并不是初始化的交界片，被初始化过的数据块并不一定被使用过.
2、实验测试
以下通过实验来进一步阐述高水位线.
2.1、创建测试表
SYS@orcl150> create table hwm as select * from dba_segments;
Table created.
SYS@orcl150> begin
2 dbms_stats.gather_table_stats('SYS','HWM');
3 end;
4 /
PL/SQL procedure successfully completed.
2.2、查高水位线信息
SYS@orcl150> select header_file,header_block from dba_segments where segment_name='HWM';
HEADER_FILE HEADER_BLOCK
----------- ------------
1 94648
--使用dump命令转储块信息到trace文件中.
SYS@orcl150> alter system dump datafile 1 block 94648;
System altered.
--查找相应的trace文件.
SYS@orcl150> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl150/orcl150/trace/orcl150_ora_40548.trc
[oracle@leo-oel150 trace]$ tail -5000f orcl150_ora_40548.trc
……
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 6
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x004172c0 ext#: 1 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 22
mapblk 0x00000000 offset: 1
……
2.3、删除数据高水位情况
删除3000行数据后确认高水位线是否有变化.
SYS@orcl150> delete from hwm where rownum<3000;
2999 rows deleted.
SYS@orcl150> commit;
Commit complete.
SYS@orcl150> alter system checkpoint;
System altered.
SYS@orcl150> alter system dump datafile 1 block 94648;
System altered.
[oracle@leo-oel150 trace]$ tail -5000f orcl150_ora_40548.trc
……
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 14 #blocks: 111
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x004173ad ext#: 13 blk#: 5 ext size: 8
#blocks in seg. hdr's freelists: 55
#blocks below: 108
mapblk 0x00000000 offset: 13
……
说明：此处与参考网址数据存在差异.
2.4、insert数据高水位情况
Insert 2000行继续观察
SYS@orcl150> insert into hwm select * from dba_segments where rownum<2001;
2000 rows created.
SYS@orcl150> commit;
Commit complete.
SYS@orcl150> alter system checkpoint;
System altered.
SYS@orcl150> alter system dump datafile 1 block 94648;
System altered.
[oracle@leo-oel150 trace]$ tail -5000f orcl150_ora_40548.trc
……
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 14 #blocks: 111
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x004173ad ext#: 13 blk#: 5 ext size: 8
#blocks in seg. hdr's freelists: 19
#blocks below: 108
mapblk 0x00000000 offset: 13
……
说明：高水位线并未变化，表明高水位线以下delete过的数据块是可以被复用的.
2.5、再次insert测试
--此时再insert 1000行.
SYS@orcl150> insert into hwm select * from dba_segments where rownum<1001;
1000 rows created.
SYS@orcl150> commit;
Commit complete.
SYS@orcl150> alter system checkpoint;
System altered.
SYS@orcl150> alter system dump datafile 1 block 94648;
System altered.
[oracle@leo-oel150 trace]$ tail -5000f orcl150_ora_40548.trc
……
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 14 #blocks: 111
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x004173ad ext#: 13 blk#: 5 ext size: 8
#blocks in seg. hdr's freelists: 2
#blocks below: 108
mapblk 0x00000000 offset: 13
……
说明：发现高水位线依旧没有变化，继续insert数据.
SYS@orcl150> insert into hwm select * from dba_segments where rownum<1001;
1000 rows created.
SYS@orcl150> commit;
Commit complete.
SYS@orcl150> alter system checkpoint;
System altered.
SYS@orcl150> alter system dump datafile 1 block 94648;
System altered.
[oracle@leo-oel150 trace]$ tail -5000f orcl150_ora_40548.trc
……
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 16 #blocks: 127
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x004173c0 ext#: 15 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 3
#blocks below: 127
mapblk 0x00000000 offset: 15
……
综上：delete并不能回收表数据，在增删改数据的过程中使得高水位线持续增长，而被删除的数据记录位置也无法100%复用，因此难免会存在碎片.比如一张100万数据的表，将全表数据delete之后，高水位线位置依旧在第100万行处，尽管此时表数据为0行，但全表扫描时仍会扫描所有已使用过的数据块，使数据库效率低下.
3、回收高水位线
oracle也提供如下回收高水位线的方法.
a、表重建, 如CATS(create table as select ...)
b、导出导入(exp/imp，expdp/impdp)
c、truncate (注意：此方法慎用)
d、shrink space，语句alter table table_name shrink space;
e、move table，语句alter table table_name move;
f、DBMS_REDEFINITION表在线重定义
以上内容基本来自以下参考网址.
参考网址：
https://blog.csdn.net/weixin_43088494/article/details/122983127
https://blog.csdn.net/Oracle_zsq/article/details/80513520?spm=1001.2101.3001.6650.5&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EOPENSEARCH%7ERate-5-80513520-blog-100293864.pc_relevant_3mothn_strategy_recovery&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EOPENSEARCH%7ERate-5-80513520-blog-100293864.pc_relevant_3mothn_strategy_recovery&utm_relevant_index=6